And another Tally type version, but seems faster to me...
CREATE FUNCTION dbo.fn_extract_chars_in_range(
@input varchar(8000) -- the string to be cleansed
,@start char(1) -- the first valid character
,@end char(1) -- the last valid character
,@maxlength smallint=10 -- maximum length to be returned
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
-- dynamic tally table
WITH
c10(c) AS(SELECT '' FROM (VALUES(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('')) as c20(c))
,c100(c) AS(SELECT '' FROM c10 a,c10 b)
,c10000(c) AS(SELECT '' FROM c100 a,c100 b)
-- the final select numbers the rows. the TOP is important for speed.
,Tally(N) AS(SELECT TOP(LEN(@input)) row_number() OVER(ORDER BY (SELECT NULL)) FROM c10000)
SELECT
CAST(
(
-- extract valid characters and concatenate them
SELECT TOP(@maxlength) substring(@input,N,1)
FROM Tally
WHERE substring(@input,N,1) BETWEEN @start AND @end
FOR XML PATH('')
) AS VARCHAR(8000)
) AS result;
SELECT result
FROM SomeTable
CROSS APPLY dbo.fn_extract_chars_in_range([phone numbers],'0','9',10)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);