Forum Discussion
How to extract number that is greter than or equal to 16 digits
Good Morning, I am using sql server 2022, I have a table I would like to extract 16 Digits or more numbers (if any exist) Please kindly help. Thank you in advance Create Table #Pay...
Hello buzza1234
Try this
SELECT ID,value
FROM #payments
CROSS APPLY STRING_SPLIT(String, ' ')
WHERE isnumeric(value) =1 AND LEN (value) >= 16Regards
Javier
Hi, Javier.
That will handle most of the example data provided but not row 3 (ID = 3), since '#4482501239835011' will fail on ISNUMERIC.
If we knew for a fact that the hash sign was the only character to worry about, we could remove it with a REPLACE prior to the STRING_SPLIT and we'd be fine, but since it could be any character, this really lends itself to a CLR approach and bringing in a proper .NET RegEx class (which in turn only lends itself to SQL Server and Azure SQL MI) and presenting that as a scalar function (since only the first match is asked for).
Cheers,
Lain
