meme
There are many inefficient ways to get only the numbers out of a string.
If you search around, there are examples of icky [scalar functions](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string), which aren't so great. There are also some [string splitters](https://www.sqlservercentral.com/articles/splitting-strings-based-on-patterns) which can do the job, but you need to use `FOR XML PATH` to reassemble the numbers-only portion.
The best solution I can come up with combines a [numbers tabl](https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1)e with an inline table valued function, but performance still isn't great.
```
CREATE OR ALTER FUNCTION dbo.get_numbers(@string NVARCHAR(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH x
AS ( SELECT TOP (LEN(@string))
ROW_NUMBER() OVER ( ORDER BY n.n ) AS x
FROM dbo.Numbers AS n )
SELECT CONVERT(NVARCHAR(4000),
( SELECT SUBSTRING(@string COLLATE Latin1_General_100_BIN2, x.x, 1)
FROM x AS x
WHERE SUBSTRING(@string COLLATE Latin1_General_100_BIN2, x.x, 1) LIKE '[0-9]'
ORDER BY x.x
FOR XML PATH('') )
) AS numbers_only;
GO
```
But even with an index (`CREATE INDEX flubber ON dbo.Users(DisplayName);`), this still takes about 12 seconds to finish on my laptop.
```
SELECT u.DisplayName, gn.*
FROM dbo.Users AS u
CROSS APPLY dbo.get_numbers(u.DisplayName) AS gn
WHERE u.DisplayName LIKE N'%[0-9]%'
```
Is there a better/faster solution? Top Answers _only_, thank you.
Top Answer
Josh Darnell
A CLR function is a good option here. This is the C# source code:
``` csharp
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text;
namespace ClrExtractNumbers
{
public static class UserDefinedFunctions
{
[SqlFunction(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true)]
public static SqlString ExtractNumbers(SqlString input)
{
var inner = input.Value;
var builder = new StringBuilder(inner.Length);
foreach (var character in inner)
{
if (char.IsDigit(character))
{
builder.Append(character);
}
}
var result = builder.ToString();
return result.Length == 0 ? null : result;
}
}
}
```
And a script for setting up the function in SQL Server:
```
DROP FUNCTION IF EXISTS
dbo.ExtractNumbersClr;
GO
DROP ASSEMBLY IF EXISTS
[ExtractNumbers];
GO
CREATE ASSEMBLY [ExtractNumbers]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300273D11CE0000000000000000E00022200B013000000C000000060000000000000E2A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000BB2900004F00000000400000B803000000000000000000000000000000000000006000000C00000018290000380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140A000000200000000C000000020000000000000000000000000000200000602E72737263000000B80300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000EF290000000000004800000002000500B82000006008000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020059000000010000110F00281000000A256F1100000A731200000A0A0C160D2B1F08096F1300000A13041104281400000A2C090611046F1500000A260917580D09086F1100000A32D8066F1600000A0B076F1100000A2C03072B0114281700000A2A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C0000002C020000237E0000980200003003000023537472696E677300000000C80500000400000023555300CC050000100000002347554944000000DC0500008402000023426C6F620000000000000002000001471502000900000000FA0133001600000100000017000000020000000100000001000000170000000F0000000100000001000000020000000000F70101000000000006002A018C02060097018C02060049005A020F00AC02000006007100140206000D0114020600D900140206007E01140206004A01140206006301140206008800140206005D006D0206003B006D020600BC0014020600A300BF01060001030D020A00F80039020A002C0039020A00260039020A00D901BB0206002B0222030600E5010D02060026020D02000000000100000000000100010081011000D002EF024100010001005020000000009600F2024A000100000001001C03090054020100110054020600190054020A00290054021000310054021000390054021000410054021000490054021000510054021000590054021000610054021500690054021000710054021000790054021000890054020600A100B5012300B100EC012700A90054020100B100E5022B00B90014033000A9001F0035008100E3012300A10008033B0020007B003C012E000B0051002E0013005A002E001B0079002E00230082002E002B0099002E00330099002E003B0099002E00430082002E004B009F002E00530099002E005B0099002E006300B7002E006B00E1002E007300EE001A00048000000100000000000000000000000000EF020000040000000000000000000000410016000000000004000000000000000000000041000A000000000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200417070656E640053797374656D446174614163636573734B696E6400477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053797374656D2E52756E74696D652E56657273696F6E696E670053716C537472696E6700546F537472696E67006765745F4C656E67746800436C72457874726163744E756D626572732E646C6C0053797374656D0053797374656D2E5265666C656374696F6E004368617200537472696E674275696C646572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730055736572446566696E656446756E6374696F6E73006765745F436861727300436C72457874726163744E756D62657273004F626A656374006F705F496D706C69636974004973446967697400696E7075740053797374656D2E546578740000000000000030FF0FB653BEB64191D481BF5BC4A7F800042001010803200001052001011111042001010E042001010208070512550E0E08030320000E032000080420010308040001020305200112550305000111510E08B77A5C561934E089060001115111510801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010002000000000016010011436C72457874726163744E756D62657273000005010000000017010012436F7079726967687420C2A920203230323000002901002461666638306538352D323762352D343235312D623536372D39353763636662393234633600000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E372E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E372E328146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501000000004171E7F100000000020000006B00000050290000500B0000000000000000000000000000100000000000000000000000000000005253445307423B59A0AD4A42A35054DF49CBC98201000000433A5C436F64655C53616E64626F785C457874726163744E756D626572735C436C72457874726163744E756D626572735C6F626A5C52656C656173655C436C72457874726163744E756D626572732E70646200E32900000000000000000000FD290000002000000000000000000000000000000000000000000000EF290000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C000000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005C03000000000000000000005C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC020000010053007400720069006E006700460069006C00650049006E0066006F0000009802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D00650000000000000000004C0012000100460069006C0065004400650073006300720069007000740069006F006E000000000043006C00720045007800740072006100630074004E0075006D0062006500720073000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004C001600010049006E007400650072006E0061006C004E0061006D006500000043006C00720045007800740072006100630074004E0075006D0062006500720073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003200300000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043006C00720045007800740072006100630074004E0075006D0062006500720073002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D0065000000000043006C00720045007800740072006100630074004E0075006D0062006500720073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.ExtractNumbersClr
(
@Input nvarchar(4000)
)
RETURNS nvarchar(4000)
WITH
RETURNS NULL ON NULL INPUT,
EXECUTE AS OWNER
AS EXTERNAL NAME ExtractNumbers.[ClrExtractNumbers.UserDefinedFunctions].ExtractNumbers;
GO
```
Finally, the slightly modified test query:
```
SELECT
u.DisplayName,
dbo.ExtractNumbersClr(u.DisplayName) AS gn
FROM dbo.Users AS u
WHERE u.DisplayName LIKE N'%[0-9]%';
```
Some notes about the C# code:
- Some small performance improvement could be gained by using `string` directly as the input parameter (instead of `SqlString`), but that would make the function less flexible (see [here][1] for details)
- The `StringBuilder` object is initialized to the size of the input string, to avoid the underlying array having to "grow" as characters are added (which is expensive)
I decided on this size after some benchmarks with different initial sizes
- Length of input string
- Half the length of the input string
- 4000 (statically)
Using a few "representative" scenarios:
- No numbers in the input string
- Half of the input string is numbers
- The input string is all numbers
Each of these scenarios was tested with small and large strings (40 vs 4000), running the function 5,000,000 times and measured the resulting runtime in milliseconds.
| |40 characters (no numbers)|40 characters (half numbers)|40 characters (all numbers)|4000 characters (no numbers)|4000 characters (half numbers)|4000 characters (all numbers)|
|-|-|-|-|-|-|-|
|SB Size = Length|750|1080|1320|21200|45000|69000|
|SB Size = Length / 2|730|1100|1500|19200|43700|69600|
|SB Size = 4000|3500|4000|4100|Same as Size = Length|Same as Size = Length|Same as Size = Length|
[1]: https://topanswers.xyz/databases?q=847