Finding numbers from a text in SQL Server.





5.00/5 (2 votes)
This is an alternative for "Finding numbers from a text in SQL Server."
Introduction
The goal of the original tip was to get the numbers from a text. The numbers could be separated by any non-digit character.
When I first looked at the original tip a few things caught my eye:
1. The many times the SUBSTRING
function was called.
2. The need to look ahead in the string to determine if the number was finished.
3. The use of the Split
function to get the result in the return table.
This is my attempt at a more efficient way of retrieving the numbers from the text.
The function
CREATE FUNCTION dbo.GetNumbersFromText( @String VARCHAR(2000) )
RETURNS @Number TABLE( Number INT )
BEGIN
DECLARE @Count INT = 0;
DECLARE @Character VARCHAR(1) = ''
DECLARE @NumberText VARCHAR(2000) = ''
WHILE @Count <= LEN( @String )
BEGIN
-- Get current character from the string.
SET @Character = SUBSTRING( @String, @Count, 1 )
-- Character is a digit, add it to end of the number already found.
IF( @Character >= '0' AND @Character <= '9' )
BEGIN
SET @NumberText = @NumberText + @Character
END
-- Character is not digit, skip it. If we already found a number store it in the result table.
ELSE
BEGIN
IF( LEN( @NumberText ) > 0 )
BEGIN
INSERT INTO @Number SELECT @NumberText
SET @NumberText = ''
END
END
-- Move onto the next character
SET @Count = @Count + 1
END
-- If the last number was at the end of the string add it to the result table.
IF( LEN( @NumberText ) > 0 )
BEGIN
INSERT INTO @Number SELECT @NumberText
SET @NumberText = ''
END
RETURN
END