Q
Problem solve Get help with specific problems with your technologies, process and projects.

Extracting leading numeric digits from a field

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

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 last published in September 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close