split a string

[chinni0719]

Hi I need to split the string which looks like
n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5

n.col1,n.col2 are columns

these are present in @columns
select @columns gives this string

the length of @column is not fixed........we may have n number of columns seperated by ',' but we need to display only first 6 out of them

i need to display only first 6 columns out of these.........

n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3

can any one plz help me out in this........... ....

[ck9663]

Hi I need to split the string which looks like
n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5

n.col1,n.col2 are columns

these are present in @columns
select @columns gives this string

the length of @column is not fixed........we may have n number of columns seperated by ',' but we need to display only first 6 out of them

i need to display only first 6 columns out of these.........

n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3

can any one plz help me out in this........... ....

While waiting for others to reply, do a string parser for now.

-- CK

[chinni0719]

i could split the colums till 6 but i got them as a table lik

columns

b.col1
n.col1
b.col2
n.col2

now iam trying to get back them to string format
lik

b.col1,n.col1,b .col2,n.col2

[chinni0719]

SELECT Item as [columns]
FROM function_to_spl it(
' n.colmn1, b.colmn1 , n.colmn2, b.colmn2 , n.colmn3 , b.colmn3 , ',')


how can i get them to a normal table to use like select * from @newtable

result:(table)

columns

n.colmn1
b.colmn1
n.colmn2
b.colmn2
n.colmn3
b.colmn3

how can i bring them to format like

n.colmn1 , b.colmn1,n.colm n2,b.colmn2,n.c olmn3,b.colmn3

thanks

[siva538]

SELECT Item as [columns]
FROM function_to_spl it(
' n.colmn1, b.colmn1 , n.colmn2, b.colmn2 , n.colmn3 , b.colmn3 , ',')


how can i get them to a normal table to use like select * from @newtable

result:(table)

columns

n.colmn1
b.colmn1
n.colmn2
b.colmn2
n.colmn3
b.colmn3

how can i bring them to format like

n.colmn1 , b.colmn1,n.colm n2,b.colmn2,n.c olmn3,b.colmn3

thanks

Here you go, the simplest way !

CREATE FUNCTION getFirstNcols(@Columns VARCHAR(1000), @Ncount INT)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @Result VARCHAR(1000)
DECLARE @Counter INT

SET @Counter = 1
SET @Result = ''

WHILE @Counter <= @Ncount
BEGIN
    SET @Result = @Result + SUBSTRING(@Columns, 
                                      LEN(@Result)+1, 
                                      CHARINDEX(',', @Columns, LEN(@Result) + 1) - LEN(@Result))
    SET @Counter = @Counter + 1

END

RETURN SUBSTRING(@Result, 1, LEN(@Result)-1)
END

you can just call this function by

DECLARE @Columns VARCHAR(1000) 

SET @Columns = 'n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5'

SELECT dbo.getFirstNCols(@Columns, 6) -- 6 can be any number of columns you want

[chinni0719]

thanku its working........ it saved lot of my time........... ..........