How to replace in sql

Question

Post reply

How to replace in sql

visweswaran28

SSC Enthusiast

Points: 117
More actions
June 9, 2010 at 11:24 pm

#233626

let output string will be,
str="01/Mar/2010,02/Mar/2010,03/Mar/2010,04/Mar/2010,05/Mar/2010"
Some manipulation, *******
Resultant string should be,
"01/Mar/2010,02/Mar/2010 03/Mar/2010,04/Mar/2010 05/Mar/2010"
Here I want to replace Comma (,) by 
Note:
I want to replace even commas only. (before 03/mar/2010 & 05/Mar/2010..like)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Try the REPLACE function.

http://msdn.microsoft.com/en-us/library/ms186862.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95461 More actions · Answer 2

This solution requires the DelimitedSplit function:

CREATE FUNCTION [dbo].[DelimitedSplit] (

@list varchar(max),

@Delimiter char(1)

)

RETURNS TABLE

AS

RETURN

-- first, need to break down into separate items. See Jeff Moden's article: -- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/

-- for how a tally table can split strings apart.

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),

Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),

ItemSplit (ItemOrder, Item) AS (

SELECT N,

RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,

CHARINDEX(@Delimiter,@Delimiter + @list + @Delimiter,N+1)-N-1)))

FROM Tally

WHERE N < LEN(@Delimiter + @list + @Delimiter)

AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = @Delimiter

)

SELECT ItemID = ROW_NUMBER() OVER (ORDER BY ItemOrder),

Item

FROM ItemSplit

And the solution is:

declare @str varchar(8000);

set @str = '01/Mar/2010,02/Mar/2010,03/Mar/2010,04/Mar/2010,05/Mar/2010';

-- DelimitedSplit splits the string apart into individual Items.

-- FOR XML PATH('') puts them back together.

-- Using % operator to determine if this is an even ItemID.

-- If so, prefix it with a comma, otherwise prefix it with a pipe.

-- (using FOR XML would replace the <> with & lt;/& gt; (without the spaces)

-- so use a temporary placeholder for them (|),

-- and then replace it with the tag.)

-- Use STUFF to remove the leading pipe character.

-- Use REPLACE to replace all pipe characters with the tag.

select REPLACE(

STUFF((select case when ItemID % 2 = 0 then ',' else '|' end + Item

from dbo.DelimitedSplit(@str, ',')

order by ItemID

FOR XML PATH(''))

,1,1,'')

,'|', ' ')

Due to the way the code window displays things, you need to remove the spaces from " ".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2