Find and Replace only the FIRST comma

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  December 18, 2013 at 6:17 am

  #302469

  Hi,

  I'd only the one occurrence of a character in a string replace.

  Excample: (this is a test, hello World 4.2 test)

  Only the first comma must Replaced with semicolon

  Like this=>: (this is a test; hello World 4.2 test)

  First comma = semikolon 🙂

  Best Regards

  Nicole 🙂

• Lowell

  SSC Guru

  Points: 323504

  More actions

  December 18, 2013 at 6:40 am

  #1674401

  the STUFF command can help you with that one:

  CREATE TABLE #tmp(ID int identity(1,1) not null primary key,[SampleString] varchar(150))

  INSERT INTO #tmp(SampleString)

  SELECT 'this is a test, hello World 4.2 test' UNION ALL

  SELECT 'other data, but with, like two commas' UNION ALL

  SELECT 'other data with no commas at all.' UNION ALL

  SELECT 'another row, with a couple of commas, i think'

  --selecting the data

  SELECT

  CASE

  WHEN CHARINDEX(',',SampleString) > 0

  THEN STUFF(SampleString,CHARINDEX(',',SampleString),1,';')

  ELSE SampleString

  END As NewString,*

  FROM #tmp;

  --updating the data?

  UPDATE #tmp

  SET SampleString =STUFF(SampleString,CHARINDEX(',',SampleString),1,';')

  WHERE CHARINDEX(',',SampleString) >0

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  December 18, 2013 at 7:05 am

  #1674413

  COALESCE(STUFF(SampleString,CHARINDEX(',',SampleString),1,';'),SampleString)

  Far away is close at hand in the images of elsewhere.
  Anon.