--Funzione che Permette di fare una
SPLIT di una stringa
/*
SELECT Orders.*
FROM Orders
WHERE (OrderID IN (10251, 10250,
10252))
--USE NORTHWIND
declare @test as varchar(50)
set @test = ' 10251 , 10250
, 10252'
SELECT Orders.*
FROM Orders
WHERE (OrderID IN (select _ID
from SplitString (@test, ',') ))
*/
declare @InputString as
varchar(8000)
declare @SplitChar as
char(1)
declare @MyTbl TABLE(_ID varchar(4000))
--set @InputString = ' AAA1 AAA2
, ABC2 ABC2 , ABCD3 ABCD3 , ABCDE4 ABCDE4 , ABCDEF5
ABCDEF5 '
--set @InputString = 's 1 s,s
2 2 s,s 3 s,s 4 s,s A s'
set @InputString = ' AAA1 , AAA2 ,a'
set @SplitChar = ','
-----------------------------------------
set @InputString = replace(@InputString, convert(varchar(10), @SplitChar), convert(varchar(10), @SplitChar) + ' ')
declare @LENInputString as
int
declare @x as int
declare @SingleChar as
varchar(4000)
set @LENInputString =
len(@InputString)
set @x = 0
while (select @x) < @LENInputString
begin
set
@SingleChar = SUBSTRING(@InputString, @x, 1)
if( select @SingleChar ) <> @SplitChar
AND (select @InputString) <> ' '
begin
declare
@y as int
set
@y = @x+1
while
(select @y) <
@LENInputString+1 AND
(select SUBSTRING(@InputString, @y, 1)) <>
@SplitChar
begin
set
@SingleChar = @SingleChar + SUBSTRING(@InputString, @y, 1)
set
@y =@y+1
end
set
@x=@y
end
if( select @SingleChar ) <> @SplitChar
AND (select @InputString) <> ' '
begin
-- print 'Char: ' +
@SingleChar
set
@SingleChar = ltrim(rtrim(@SingleChar))
INSERT
INTO @MyTbl (_ID) VALUES (@SingleChar)
end
set @x = @x+1
end
SELECT _ID FROM
@MyTbl
/*
ALTER FUNCTION dbo.SplitString
(
@InputString varchar(8000),
@SplitChar char(1)
)
RETURNS @MyTbl TABLE(_ID
varchar(4000))
AS
BEGIN
set @InputString =
replace(@InputString, convert(varchar(10), @SplitChar), convert(varchar(10),
@SplitChar) + ' ')
declare @LENInputString as int
declare @x as int
declare @SingleChar as varchar(4000)
set @LENInputString =
len(@InputString)
set @x = 0
while (select @x) <
@LENInputString
begin
set @SingleChar =
SUBSTRING(@InputString, @x, 1)
if( select @SingleChar )
<> @SplitChar AND (select @InputString) <> ' '
begin
declare @y as int
set @y = @x+1
while (select @y) <
@LENInputString+1 AND (select SUBSTRING(@InputString, @y, 1)) <>
@SplitChar
begin
set @SingleChar =
@SingleChar + SUBSTRING(@InputString, @y, 1)
set @y =@y+1
end
set @x=@y
end
if( select @SingleChar )
<> @SplitChar AND (select @InputString) <> ' '
begin
-- print 'Char: ' +
@SingleChar
set @SingleChar =
ltrim(rtrim(@SingleChar))
INSERT INTO @MyTbl (_ID)
VALUES (@SingleChar)
end
set @x = @x+1
end
return
end
*/