Want to split variable in SQL[modified]

[xodeblack]

I have variable @keys which value is Declare @keys varchar(max) set @keys = '44,333,234,125,3,3445' I want to split above variable in following from

44
333
234
125
3
3445

the length of @keys can be 4000 character

Dre@m is not th@t wh@t u see in sleep.Dre@m is the thing which does not allow u to sleep

modified on Saturday, September 12, 2009 9:52 AM

[i i i]

DECLARE @NextString NVARCHAR(40) DECLARE @Pos INT DECLARE @NextPos INT DECLARE @String NVARCHAR(40) DECLARE @Delimiter NVARCHAR(40) SET @String ='softdev,sup' SET @Delimiter = ',' SET @String = @String + @Delimiter SET @Pos = charindex(@Delimiter,@String) WHILE (@pos <> 0) BEGIN SET @NextString = substring(@String,1,@Pos - 1) SELECT @NextString -- Show Results SET @String = substring(@String,@pos+1,len(@String)) SET @pos = charindex(@Delimiter,@String) END Result - Softdev - sup

Best Of Regards, SOFTDEV If you have knowledge, let others light their candles at it

[tagyurit]

do a string replace: pseudocode: string sLineFeed = "\n"; (or vbCrLf for Visual Basic or <br> for HTML) string sSource = "A,B,C,D,E,F,G"; string sDestintion = replace(sSource,",",sLineFeed) Rob http://tagyurit.com

r

[Abhishek Sur]

Use this function :

CREATE FUNCTION [dbo].[FN_STRINGTOTABLE](@P_STRING VARCHAR(8000), @P_DELIMITER CHAR(1))
RETURNS @P_TEMPTABLE TABLE
(ITEMS VARCHAR(8000))
AS
BEGIN
DECLARE @V_IDX INT
DECLARE @V_SLICE VARCHAR(8000)

SELECT @V\_IDX = 1        
    IF LEN(@P\_STRING)<1 OR @P\_STRING IS NULL  RETURN        
   
WHILE @V\_IDX != 0        
BEGIN        
    SET @V\_IDX = CHARINDEX(@P\_DELIMITER,@P\_STRING)        
    IF @V\_IDX	!=0        
        SET @V\_SLICE = LEFT(@P\_STRING,@V\_IDX - 1)        
    ELSE        
        SET @V\_SLICE = @P\_STRING        
       
    IF(LEN(@V\_SLICE)>0)   
        INSERT INTO @P\_TEMPTABLE(ITEMS) VALUES(@V\_SLICE)        

    SET @P\_STRING = RIGHT(@P\_STRING,LEN(@P\_STRING) - @V\_IDX)        
    IF LEN(@P\_STRING) = 0 BREAK        
END    

RETURN
END

This will work great. :rose:

Abhishek Sur

---

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.

[PIEBALDconsult]

Why? :wtf: