T-SQL: Get number from string

When you are working with custom software over your database, you can easily find some data you need to clean up. It can be trash from poorly written code, or users can write rubbish if no controls are established. So next couple of posts will be about searching and updating this kind of trash values.
First is getting a number from a string.
So, I had a task of exporting phone numbers in unified format, but numbers were in all variety:
+(385)11 1111-111
0038511/1111-111
etc.
I used to write WHILE loop which would read a character by character in string, and then get the number in numeric string.
Then I found out that there are couple of functions PATINDEX and STUFF which can help me out quickly.
This idea cam from this site SQLAuthoritiy from Pinal Dave.

PATINDEX(‘%pattern%’,expression) – returns first position of pattern
image
STUFF(expression,start,length,replace_string) – replaces length in expression from position start with replace string
image

Now, when we got this clear, create a function which will return only string. Function is created on database level. So if you want to use it everywhere you must have permission and use it like this: database.schema.function

use database
go

CREATE FUNCTION fnGetNumberFromString
(@strInput VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intNumber int
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput) – set first location of number
BEGIN
WHILE @intNumber > 0 –until position number of number in string is null or 0
BEGIN
SET @strInput = STUFF(@strInput, @intNumber, 1, ” ) –replace string with ‘blank’
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput )–next position of number
END
END
RETURN ISNULL(@strInput,0)
END
GO

Now just to test it

image 

That’s how you get numbers from string.

Good luck