Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jun 2011
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Need help for extracting the integer values from a string

    Hello!

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

    Example:
    Code:
    Thisisastring123
    to get

    Code:
    123
    but, if like this

    Code:
    Hello3Ape1234
    i need

    Code:
    1234
    , not the extra 3.

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

    Lucas

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    \me feels cheap saying this. Would you accept php? :P

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,206
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    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...

    Code:
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,206
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Here. I actually tried it.
    Code:
    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.

  • #5
    New Coder
    Join Date
    Jun 2011
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks a lot!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •