December 18, 2013 at 6:17 am
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 🙂
December 18, 2013 at 6:40 am
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
December 18, 2013 at 7:05 am
COALESCE(STUFF(SampleString,CHARINDEX(',',SampleString),1,';'),SampleString)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply