Extracting numerical value from a varchar field

[lilal111]

I am working with an SQL Server Database that has a field name customvalue. This is a varchar field and has everything from text, dates, numbers ect... in it. I am trying to extract only the numerical values from the field.

employee customvalue
112 12
112 13
112 JOE BLOW
112 07/29/2008
140 Expense

For example only pull out the 12 and 13 values.

Thanks much!!

 

[dhulbert]

SELECT  EMPLOYEE, CUSTOMVALUE
FROM    TABLE
WHERE   ISNUMBER(CUSTOMVALUE) = 1

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[djj55]

dhulbert, did you mean ISNUMERIC?


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

IsNumeric will return true for some odd things, like 2e4 (scientific notation) and $40.00 (money).

In my opinion, when dealing with numbers stored in varchar columns, it's best to be as restrictive as possible which is likely to give you better results and/or less conversion errors.

For example, if you want just positive integers, then I would suggest:

SELECT  EMPLOYEE, CUSTOMVALUE
FROM    TABLE
WHERE   ISNUMERIC('-' + CUSTOMVALUE + '.0e0') = 1
        AND CUSTOMVALUE Like '[0-9]%'

Adding the additional where clause criteria (which is sargable) is likely to speed up the execution of this query, especially if there is an index on the table that has CUSTOMVALUE as the first column in the index.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom