Extracting leading numeric digits from a field

Extracting leading numeric digits from a field

I have a table with the following field containing values like:

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

130,123,140
1,2,3
45FS-d,54,56
120
2134
1000,2000,3000

How do I obtain the following values without pulling my hair out?

130
1
45
120
2134
1000

It's easy if your database has the right built-in functions.

Here's the solution for Microsoft SQL Server:

select case
        when patindex('%[^0-9]%',thefield) > 0
        then left(thefield,
             patindex('%[^0-9]%',thefield) - 1 )
        else thefield
       end as thesubstring
  from thetable

In this example, the PATINDEX function searches the field for the pattern [^0-9] which means any character except 0 through 9. PATINDEX returns the position of the first occurrence. Thus, if PATINDEX finds a non-numeric, subtract 1 to get the length of the numeric substring, and use that in the LEFT function. If PATINDEX does not find a non-numeric, it returns 0, so in that case, use the entire field.

If instead of SQL Server you have some other database system, then your mileage, as they say, may vary.

For More Information


This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top