'1' like '[0-9]' - работает!

The PHONE_CLEAN Function
This function will remove all non-numeric characters from a string.


Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='(406) 555-1212',
@Str2='';

With PhoneClean as
(
Select
Case
when SubString(@Str1,1,1) like '[0-9]' then SubString(@Str1,1,1)
else ''
End[Chr],
1[Idx]
Union All
Select
Case
when SubString(@Str1,Idx+1,1) like '[0-9]' then SubString(@Str1,Idx+1,1)
else ''
End,
Idx+1
from PhoneClean
where (Idx+1)<=Len(@Str1)
)
Select
@Str2=@Str2+Chr
from PhoneClean
option (MaxRecursion 0);
Select
@Str2;

Источник http://www.sqlservercentral.com/articles/T-SQL/63591/

Развернуть список в таблицу (несколько вариантов)

Задача - из строки 1,22,334
получить таблицу
1
22
334

ИТОГ - самый быстрый (пока) вариант 4
==== но там используется EXEC неприемлемый для функции
Далее по скорости лучший вариант 5

UPDATE - использование C#

UPDATE2:(XML) Вариант 3а

=================================================
CREATE FUNCTION [dbo].[func$COM$GetListAsTable]
(
@List varchar(1024),
@Delim varchar(1) = ','
)
RETURNS @Table TABLE (Element varchar(50))
AS
BEGIN

IF LEN(@List) = 0
RETURN;

/* --вариант 1 (в лоб)
DECLARE @Pos int;

IF Right(@List, 1) != @Delim
SET @List = @List + @Delim;

SET @pos = charindex(@Delim, @List);
WHILE @pos > 0
BEGIN
INSERT INTO @Table
VALUES (substring(@List, 1, @Pos - 1));

SET @List = substring(@List, @Pos+1, 1024);
SET @Pos = charindex(@Delim, @List);
END

-- вариант 2 (через CTE)
With Parse as
(
Select
SubString@List,1,CharIndex(',', @List)-1)[Element],
CharIndex(',', @List)[Idx]
Union All
Select
SubString@List,Idx+1,case when CharIndex(',', @List, Idx+1)=0 then 255 else CharIndex(',', @List, Idx+1) end -1-Idx),
CharIndex(',', @List, Idx+1)
from Parse
where Idx>0
)

INSERT INTO @Table
Select Element
from Parse
--option (MaxRecursion 0);

*/

-- вариант 3 (через XML)
declare @MyXMLData XML
set @MyXMLData = ''+
replace(@List,',','')+
''
INSERT INTO @Table
select x.item.value('Element[1]','nvarchar(15)')
from @MyXMLData.nodes('/Rows/Row')AS x(item)

-- вариант 3а
declare @CommaList varchar(max)
set @CommaList='ABC'+replicate(cast(',ABC' as varchar(max)),4999)

declare @XMLList xml
set @XMLList=cast(''+replace(@CommaList,',','')+'' as xml)
(вот оно!)
select x.i.value('.','varchar(3)')
from @XMLList.nodes('i') x(i)
или (еще лучше)
select x.i.value('(./text())[1]','varchar(3)')
from @XMLList1.nodes('i') x(i)
(а так хуже)
select x.i.value('.','varchar(3)')
from (select XMLList=cast(''+replace(@CommaList,',','')+'' as xml).query('.')) a
cross apply XMLList.nodes('i') x(i)
источник



-- вариант 4 (через XML)
-- быстрее чем 3
-- НО! с EXEC который невозможен в функции
declare @MyXMLData varchar(max)
set @MyXMLData = ''+
replace(@List,@Delim,'')+
''

DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @MyXMLData

INSERT INTO @Table
select x.Element from
OPENXML (@DocHandle, '/Rows/Row',1) WITH (Element int) AS x
EXEC sp_xml_removedocument @DocHandle

-- вариант 5
declare @pos int;
declare @len int;
declare @number varchar(10);
declare @curchar char;
declare @workStr varchar(MAX);
set @workStr=@List+',';

set @len=len(@workStr);

set @pos=1;
set @number='';

while(@pos<@len+1)
BEGIN
set @curchar=substring(@workStr, @pos, 1);
if(@curchar<>',')
BEGIN
set @number=@number+@curchar;
END
else
BEGIN
--print (@number);
INSERT INTO @Table VALUES (@number);
set @number='';
END

set @pos=@pos+1;
END

RETURN
END

--пример вызова
select * from dbo.func$COM$GetListAsTable('1,2', ',')

Вариант 6

[SqlFunction(FillRowMethodName = "FillRow",Name = "fn_split_clr", TableDefinition = "value bigint")]

public static IEnumerable fn_split_clr([SqlFacet(MaxSize=-1)]SqlString value)

{

return value.Value.Split(new []{';'});

}

public static void FillRow(Object obj, out SqlInt64 value)

{

Int64 res;

value = Int64.TryParse((string)obj,out res) ? new SqlInt64(res) : new SqlInt64();

}

Источник http://www.sqlservercentral.com/articles/XML/63633/

Еще статьи на эту тему http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
http://www.simple-talk.com/sql/t-sql-programming/faking-arrays-in-transact-sql/

UPDATE

SCOPE_IDENTITY

MSSQL
Получить значение автоинкремента - SCOPE_IDENTITY() - в текущей сессии, @@IDENTITY в текущей но после триггера, IDENT_CURRENT(_table_name) - для всех сессий