By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

split a string

P: 18
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...............
Apr 15 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Apr 15 '08 #2

P: 18
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
Apr 15 '08 #3

P: 18
SELECT Item as [columns]
FROM function_to_split(
' 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.colmn2,b.colmn2,n.colmn3,b.colmn3

thanks
Apr 15 '08 #4

P: 44
SELECT Item as [columns]
FROM function_to_split(
' 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.colmn2,b.colmn2,n.colmn3,b.colmn3

thanks


Here you go, the simplest way !

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE FUNCTION getFirstNcols(@Columns VARCHAR(1000), @Ncount INT)
  3. RETURNS VARCHAR(1000) AS
  4. BEGIN
  5. DECLARE @Result VARCHAR(1000)
  6. DECLARE @Counter INT
  7.  
  8. SET @Counter = 1
  9. SET @Result = ''
  10.  
  11. WHILE @Counter <= @Ncount
  12. BEGIN
  13.     SET @Result = @Result + SUBSTRING(@Columns, 
  14.                                       LEN(@Result)+1, 
  15.                                       CHARINDEX(',', @Columns, LEN(@Result) + 1) - LEN(@Result))
  16.     SET @Counter = @Counter + 1
  17.  
  18. END
  19.  
  20. RETURN SUBSTRING(@Result, 1, LEN(@Result)-1)
  21. END
  22.  
  23.  

you can just call this function by

Expand|Select|Wrap|Line Numbers
  1. DECLARE @Columns VARCHAR(1000) 
  2.  
  3. SET @Columns = 'n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5'
  4.  
  5. SELECT dbo.getFirstNCols(@Columns, 6) -- 6 can be any number of columns you want 
  6.  
Apr 15 '08 #5

P: 18
thanku its working........ it saved lot of my time.....................
Apr 15 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.