Fun with Numbers in Transact-SQL Queries

By : Vyas Kondreddi Jun 30, 2002

Printer friendly

Here's another scenario! How about getting just the numbers from a given string? Remember those cell phones pulling out the numbers from SMS/Text messages? :-) Of course, this problem can be solved easily using a procedural approach by looping through the string from starting to the end and discarding non-numeric characters. Performance difference between these two approaches should be minimal. I haven't actually tried benchmarking, but will update this article if and when I do. Anyway, here's the code:

DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'My Number is: 0771 543 2360'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT TOP 100 PERCENT Number, SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Number
) AS Derived
WHERE Val LIKE '[0-9]' 

SELECT @input AS 'Original string',
@output AS 'Extracted numbers'

If you are running SQL Server 2000, the above scripts can be converted to handy User Defined Functions (UDF). I said "handy" because, UDFs can be used in a lot of places like column list, WHERE clause of a SELECT statement. Here are the User Defined Function scripts:

User Defined Function 1: SortString() 

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'SortString'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.SortString
END
GO

CREATE FUNCTION dbo.SortString
(
@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @output varchar(8000), @len smallint
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val 
FROM 
(
SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val
) AS Derived

RETURN @output
END
GO

SELECT dbo.SortString('911Abcdzyxfghjie999') AS 'Sorted string'
GO

User Defined Function 2: ExtractUniqueChars() 

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ExtractUniqueChars'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.ExtractUniqueChars
END
GO

CREATE FUNCTION dbo.ExtractUniqueChars
(
@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @output varchar(8000), @len smallint
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val
) AS Derived

RETURN @output
END
GO

SELECT dbo.ExtractUniqueChars('cba abc bac') AS 'Unique characters (Sorted)'
GO