In many high end programming languages like C#,Java,JavaScript etc. they provide a feature call as regular expression to do this kind of operation and it is quite handy also for this kind of job. However, SQL Server is doesn't provide such an option to do so. But with a little of effort and trick we can obtain that. This article is mainly focus on how to do so.

Introduction

Sometimes we encounter the situation where we need to extract the numbers from the set of text. E.g. suppose we have a text like

My first mobile number:91161181100. Second mobile number:- 1111111111. Third one is: 1212121212

And we need to extract the mobile numbers in a way so the the final output must be

91161181100,1111111111,1212121212

In many high end programming languages like C#,Java,JavaScript etc. they provide a feature call as regular expression to do this kind of operation and it is quite handy also for this kind of job.However, SQL Server is doesn't provide such an option to do so.But with a little of effort and trick we can obtain that.This article is mainly focus on how to do so.

A careful observation can help us to look into the way the texts are arranged.In the first row, the numbers are arranged in three different locations.In the second case they are located next to each other but are separated by commas while in the last one , the numbers and the alphabets are aligned to each other.In another word, it is highly unstructured data.However, we need to process it to figure out the respective and needed numbers and the presentation should be as under

Using the code

In order to solve this problem,first we will split the textual data with space(' ') as the delimiter as shown under

As can be figure out that by using the PATINDEX, we can easily be able to detect out which texts are having alphanumeric characters and which are pure alphabets. So, as a second step, we will eliminate those which does have only alphabets as shown under

So now we are very close to our solution.As a final step we need to the FORXMLPATH and group by the items by using the 'Name' column

SELECT
Name
,STUFF(( SELECT ',' + c1.AllNumeric
FROM OnlyNumericCTE c1
WHERE c1.Name = c2.Name
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,1,'') AS MobileNumbers
FROM OnlyNumericCTE c2
GROUP BY Name

Reference

Conclusion

Though we have seen the way to extracting numeric values from textual data, however, we will not recommend T-SQL for doing this kind of operation as huge/heavy string processing is not the role for which Sql-Server is meant for.Better to use the regular expression technique of the programming languages like C#,Java,JavaScript etc.Thanks for reading.Zipped file is attached herewith.