// Автор: Колосов В.В.
// Дата создания: 10.05.2017
// Назначение: SQL C# функция, возвращает значение найденной группы, соответствующей образцу регулярного выражения.
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlChars regex.Replace( SqlString input, SqlString pattern, SqlString replacement )

    {

        if (true == input.IsNull)
            return SqlChars.Null;

        Regex regex = new Regex(pattern.Value);

        return new SqlChars(regex.Replace(input.Value, replacement.Value));

    }

};

select dbo.RegexReplace('Phone:8(912)29-5555-5','\D+','')

declare @t table (s varchar(30));
insert into @t
values
 ('"Phone:8(912)29-5555-5"'),
 ('"Phone:8(912)295-55-55"');

select
 t.*, cast(l.x.query('item/digit/text()') as varchar(100))
from
 @t t cross apply
 (select cast(cast('' as xml).query('sql:column("t.s")') as varchar(100))) a(s) cross apply
 (select replace(a.s, '0', '<digit>0</digit>')) b(s) cross apply
 (select replace(b.s, '1', '<digit>1</digit>')) c(s) cross apply
 (select replace(c.s, '2', '<digit>2</digit>')) d(s) cross apply
 (select replace(d.s, '3', '<digit>3</digit>')) e(s) cross apply
 (select replace(e.s, '4', '<digit>4</digit>')) f(s) cross apply
 (select replace(f.s, '5', '<digit>5</digit>')) g(s) cross apply
 (select replace(g.s, '6', '<digit>6</digit>')) h(s) cross apply
 (select replace(h.s, '7', '<digit>7</digit>')) i(s) cross apply
 (select replace(i.s, '8', '<digit>8</digit>')) j(s) cross apply
 (select replace(j.s, '9', '<digit>9</digit>')) k(s) cross apply
 (select cast('<item>' + k.s + '</item>' as xml)) l(x);

DECLARE 
       @number           varchar(99) = ' 8 (912) 295-55-55 ',
       @number_processed varchar(99)

SET @number_processed = 
(
    SELECT
        SUBSTRING (@number, v.number, 1) 
    FROM 
        dbo.number AS v
    WHERE 
        v.number < LEN(@number)
        AND SUBSTRING (@number, v.number, 1) LIKE '[0-9]'
    FOR XML PATH('')
)

PRINT @number_processed

DECLARE 
       @number           varchar(99) = ' 8 (912) 295-55-55 ',
       @number_processed varchar(99)

SET @number_processed = 
(
    SELECT
        SUBSTRING (@number, v.number, 1) 
    FROM 
        dbo.number AS v
    WHERE 
        v.number < LEN(@number)
        AND SUBSTRING (@number, v.number, 1) LIKE '[0-9]'
    FOR XML PATH('')
)

PRINT @number_processed

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 797 ms,  elapsed time = 800 ms.

 SQL Server Execution Times:
   CPU time = 406 ms,  elapsed time = 445 ms.

Msg 9416, Level 16, State 1, Line 40
XML parsing: line 1, character 10, hexadecimal digit expected

Msg 9416, Level 16, State 1, Line 40
XML parsing: line 1, character 10, hexadecimal digit expected

 SQL Server Execution Times:
   CPU time = 3969 ms,  elapsed time = 3976 ms.

create function dbo.f_only_digits_in_jazz(@str nvarchar(1000))
returns nvarchar(1000)
as
begin

declare @i int,@str_out nvarchar(1000)

select @i=LEN(@str),@str_out=''

while(@i>0)
begin
	if ascii(RIGHT(@str,1)) between ascii('0') and ascii('9')
	set @str_out=RIGHT(@str,1)+@str_out
	
	set @str=LEFT(@str, LEN(@str)-1)
	set @i=@i-1
end

return @str_out
end
go

select dbo.f_only_digits_in_jazz('Phone:8(912)29-5555-5')

alter function dbo.f_only_digits_in_jazz(@str nvarchar(1000))
returns nvarchar(1000)
as
begin

declare @i int,@str_out nvarchar(1000)

select @i=LEN(@str),@str_out=''
,@str=REPLACE(@str,' ','~')--против пробеллов

while(@i>0)
begin
	if ascii(RIGHT(@str,1)) between ascii('0') and ascii('9')
	set @str_out=RIGHT(@str,1)+@str_out
	
	set @str=LEFT(@str, LEN(@str)-1)
	set @i=@i-1
end

return @str_out
end

--select dbo.f_only_digits_in_jazz('Phone: 8 (912)29-55 55-5 ')

alter function dbo.f_only_digits_in_jazz(@str nvarchar(1000))
returns nvarchar(1000)
as
begin

declare @i int,@str_out nvarchar(1000)

select @i=LEN(@str),@str_out=''
,@str=REPLACE(rtrim(@str),' ','~')--против пробеллов

while(@i>0)
begin
	if ascii(RIGHT(@str,1)) between ascii('0') and ascii('9')
	set @str_out=RIGHT(@str,1)+@str_out
	
	set @str=LEFT(@str, LEN(@str)-1)
	set @i=@i-1
end

return @str_out
end

--select dbo.f_only_digits_in_jazz('  7 6 98 345 Phone: 8 (912)29-55 55-5   ')

 SQL Server Execution Times:
   CPU time = 1156 ms,  elapsed time = 1421 ms.

Invalid object name 'dbo.number'.