2007-01-27

SQL Split Function


una funzione che può tornare utile nel caso si utilizzi l'operatore IN (@Param)... ;-)


--Funzione che Permette di fare 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

*/

Nessun commento:

Posta un commento