...

View Full Version : Need help for extracting the integer values from a string



think123
07-24-2011, 09:34 AM
Hello!

I need an SQL query which can extract the integers from a string. Is there any possible way?

Example:


Thisisastring123


to get



123


but, if like this



Hello3Ape1234


i need



1234

, not the extra 3.

Is there a way? Great Thanks to anyone who can help me answer this question!

Lucas
:thumbsup: :thumbsup: :thumbsup: :thumbsup: :thumbsup:

BluePanther
07-24-2011, 11:22 PM
\me feels cheap saying this. Would you accept php? :P

Old Pedant
07-25-2011, 02:31 AM
I think it could be done if you have a reasonable maximum on the number of trailing digits you will look for.

It's ugly as pig snot code, but...



SELECT CAST( RIGHT( field,
( CASE WHEN field REGEXP '[0-9][0-9][0-9][0-9]$' THEN 4
WHEN field REGEXP '[0-9][0-9][0-9]$' THEN 3
WHEN field REGEXP '[0-9][0-9]$' THEN 2
WHEN field REGEXP '[0-9]$' THEN 1 END )
) AS UNSIGNED INTEGER ) AS theIntValue
FROM table


Do you see it? If there are 4 digits on the end of the field, then we use RIGHT( field, 4 ); if 2 digits, then RIGHT( field, 2 )

And then we cast the digits to integer.

If you need to handle more than 4 digits, I'm sure you can see how to extend it.

Old Pedant
07-25-2011, 02:32 AM
Here. I actually tried it.


mysql> select * from demoMixed;
+--------------------+
| mixed |
+--------------------+
| now is the time123 |
| alpha77 |
| beta4 |
| gamma8888 |
+--------------------+
4 rows in set (0.02 sec)

mysql> SELECT CAST( RIGHT( mixed,
-> ( CASE WHEN mixed REGEXP '[0-9][0-9][0-9][0-9]$' THEN 4
-> WHEN mixed REGEXP '[0-9][0-9][0-9]$' THEN 3
-> WHEN mixed REGEXP '[0-9][0-9]$' THEN 2
-> WHEN mixed REGEXP '[0-9]$' THEN 1 END )
-> ) AS UNSIGNED INTEGER ) AS theIntValue
-> FROM demoMixed;
+-------------+
| theIntValue |
+-------------+
| 123 |
| 77 |
| 4 |
| 8888 |
+-------------+
4 rows in set (0.03 sec)


Worked like a charm.

think123
07-25-2011, 07:41 AM
Thanks a lot!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum