Fun with Numbers in Transact-SQL Queries

User Defined Function 3: ExtractNumbers() 

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

CREATE FUNCTION dbo.ExtractNumbers
(
@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 Number, SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Number
) AS Derived
WHERE Val LIKE '[0-9]' 

RETURN @output
END
GO

SELECT dbo.ExtractNumbers('My Number is: 0771 574 0609')
GO

If you can spare a few KiloBytes of memory, you can pin the Numbers table into memory, so that the pages of this table remain in memory, once read into memory. This is okay with smaller tables like the 'Numbers' table, but do not try this with larger tables as that can negatively impact SQL Server's performance. The following command can be used to pin the 'Numbers' table in memory (Also see DBCC PINTABLE in SQL Server Books Online (BOL)): 

EXEC sp_tableoption 'Numbers', 'pintable', 'true'
GO

In most cases, it may not be necessary to pin a table in memory, if it is accessed frequently, and SQL Server has enough data cache to hold this table in memory.

That concludes this article. I concentrated only on string manipulations, but for sure, there's more that can be done with a table of numbers. Watch out for more articles, code samples in the near future at my code library. Have fun!

Published with the express written permission of the author. Copyright 2002.