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<br/>03/Mar/2010,04/Mar/2010<br/>05/Mar/2010"

  Here I want to replace Comma (,) by <br/>

  Note:

  I want to replace even commas only. (before 03/mar/2010 & 05/Mar/2010..like)

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  June 9, 2010 at 11:40 pm

  #1178446

  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

  June 10, 2010 at 5:31 am

  #1178542

  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 < br > tag.)

  -- Use STUFF to remove the leading pipe character.

  -- Use REPLACE to replace all pipe characters with the < br > 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,'')

  ,'|', '< br >')

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

  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