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:
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.
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
CREATE FUNCTION fnGetNumberFromString
DECLARE @intNumber int
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput) – set first location of number
WHILE @intNumber > 0 –until position number of number in string is null or 0
SET @strInput = STUFF(@strInput, @intNumber, 1, ” ) –replace string with ‘blank’
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput )–next position of number
Now just to test it
That’s how you get numbers from string.