Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find and Replace only the FIRST comma Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 6:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:05 AM
Points: 53, Visits: 179
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
Post #1524087
Posted Wednesday, December 18, 2013 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1524102
Posted Wednesday, December 18, 2013 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 7,051, Visits: 6,808
COALESCE(STUFF(SampleString,CHARINDEX(',',SampleString),1,';'),SampleString)


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

Anon.

Post #1524115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse