SQL Server Magazine

How can I retrieve only the numeric part of a string? For example, given the string ZUA123456789, I want only 123456789.

If you know the letters will always appear at the beginning of the string, you can use a technique such as

SUBSTRING(col1, charindex(col1,'1',), len(col1))

(Note that the preceding example isn't complete and won't run.) But if the letters can appear at the beginning or the end of the field, the code to strip out the letters is more complicated. Unsigned integer fields are pretty easy to handle; but fields defined as signed integer, decimal, or real data types are harder because you must localize and deal with the ',' or '.' and the 'E' and '-' characters in the fields. Listing 1's code example shows how you might strip the letters from a string.

End of Article