A common question among forum is a SQL function to extract number from string. Let’s see how this can be achieved.

One way to achieve this is with while loop as shown below.

The logic is to loop through the string and replace all characters with empty string until we are only left with numbers. The PATINDEX(‘%[^0-9]%’,@string) returns the starting position of a character/alphabet. So we loop until it returns 0 i.e. there are no more alphabets/characters present in a string. It then replaces the characters/alphabets with empty space with in the while loop. Thus, we are left only with numbers once the while loop finishes.

The T-SQL is a set based language. The solution given below uses tally table to extract numbers in a set based way.

The above query breaks the string into rows of single characters and then concatenates only the rows with numeric values, thus eliminating all characters/alphabets.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook