String Manipulation

• sql_novice_2007

  Hall of Fame

  Points: 3307

  More actions

  April 23, 2015 at 9:20 am

  #297351

  I have following strings in the full name field.

  first,,

  first,,last

  ,middle,last

  I want to have the following results

  first

  first,last

  middle,last

  need to remove the unwanted , in the full name string.

  How can I do this.

  Thanks.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 23, 2015 at 9:33 am

  #1793035

  sql_novice_2007 (4/23/2015)

  ---

  I have following strings in the full name field.

  first,,

  first,,last

  ,middle,last

  I want to have the following results

  first

  first,last

  middle,last

  need to remove the unwanted , in the full name string.

  How can I do this.

  Thanks.

  WITH PartialNames AS (

  SELECT * FROM (VALUES('first,,'),('first,,last'),(',middle,last')) d (PartialName)

  )

  SELECT

  PartialName,

  REPLACE(PartialName,',,',','),

  CASE WHEN LEFT(PartialName,1) = ',' THEN STUFF(PartialName,1,1,'') ELSE PartialName END,

  REPLACE(CASE WHEN LEFT(PartialName,1) = ',' THEN STUFF(PartialName,1,1,'') ELSE PartialName END,',,',',')

  FROM PartialNames

  ^{“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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 23, 2015 at 10:07 am

  #1793047

  This might be more work for the engine but I was having some fun with the DelimitedSplit8k splitter.

  WITH PartialNames AS (

  SELECT * FROM (VALUES('first,,'),('first,,last'),(',middle,last')) d (PartialName)

  )

  SELECT

  STUFF((SELECT ',' + item

  FROM DelimitedSplit8k(PartialName, ',')

  WHERE LEN(item) > 0

  FOR XML PATH('')), 1, 1, '')

  FROM PartialNames

  References:

  DelimitedSplit8k: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  Concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 23, 2015 at 10:11 am

  #1793050

  And another solution using nested REPLACE.

  WITH PartialNames AS (

  SELECT * FROM (VALUES('first,,'),('first,,last'),(',middle,last')) d (PartialName)

  )

  SELECT REPLACE( REPLACE( RTRIM( LTRIM( REPLACE( PartialName, ',', ' '))), ' ', ','), ',,', ',')

  FROM PartialNames

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2