Can any one please guide,how to split this result,

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 22, 2010 at 2:20 am

  #1240755

  ColdCoffee (10/21/2010)

  ---

  Chris Morris-439714 (10/21/2010)

  ---

  ColdCoffee (10/21/2010)

  ---

  And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.

  There's a trick for that...put them in then

  REPLACE(',' + EmpName + ',',',,',',')

  fantastic thought CM, but there is a catch here...

  Assume the string is like this

  ',an,empty,space,comes,between,this,and, ,this,'

  Then the REPLACE will collapse the string... correct me if am wrong..

  DECLARE @EmpName VARCHAR(60)

  SET @EmpName = ',an,empty,space,comes,,between,this,and, ,this,'

  SELECT REPLACE(',' + @EmpName + ',',',,',',')

  -- result

  ',an,empty,space,comes,between,this,and, ,this,'

  -- which would result in an extra row in the output

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 22, 2010 at 2:24 am

  #1240758

  sharath.chalamgari (10/21/2010)

  ---

  ...

  Please let me know if i am wrong in any way

  Nope, if the results are correct then it's not wrong. It may not be the fastest show in the west, however. The splitstring method which ColdCoffee pointed out in a previous post is faster, it's also rigorously tried and tested - always a good thing.

  Edit: spelling.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  October 22, 2010 at 2:42 am

  #1240764

  Chris Morris-439714 (10/22/2010)

  ---

  ColdCoffee (10/21/2010)

  ---

  Chris Morris-439714 (10/21/2010)

  ---

  ColdCoffee (10/21/2010)

  ---

  And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.

  There's a trick for that...put them in then

  REPLACE(',' + EmpName + ',',',,',',')

  fantastic thought CM, but there is a catch here...

  Assume the string is like this

  ',an,empty,space,comes,between,this,and, ,this,'

  Then the REPLACE will collapse the string... correct me if am wrong..

  DECLARE @EmpName VARCHAR(60)

  SET @EmpName = ',an,empty,space,comes,,between,this,and, ,this,'

  SELECT REPLACE(',' + @EmpName + ',',',,',',')

  -- result

  ',an,empty,space,comes,between,this,and, ,this,'

  -- which would result in an extra row in the output

  The sample i gave you is wrong. Try this CM:

  DECLARE @EmpName VARCHAR(60)

  SET @EmpName = ',an,empty,space,comes,,between,this,and,,this,'

  -- IN the above statement, between the commas of "and" and "this" ,there is not even a single space

  Now, upon applying your replace, i get

  ,an,empty,space,comes,between,this,and,this,

  still, the trailing and leading commas are there.

  Now if i tweak your REPLACE to this REPLACE(',' + @EmpName + ',',',,',''), i get

  an,empty,space,comesbetween,this,andthis