sqlog

sql & co
posts - 78, comments - 14, trackbacks - 1

SQL Split Function

--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

 

*/

Print | posted on Wednesday, January 24, 2007 4:17 PM |

Powered by:
Powered By Subtext Powered By ASP.NET