Replace a certain character in a string but not all

• cabby

  SSC Enthusiast

  Points: 180

  More actions

  January 5, 2011 at 11:00 am

  #230130

  Hi All,

  I'm not sure if there is a post like this before can anyone help me i'm trying to replace a character from a returned query

  1, 2, 3, 4 to 1, 2, 3 and 4

  Hope someone can help me, i'm thinking of using RIGHT and SUBSTRING Function to get the last comma position but i can't seem to get the exact position

  
  cabby

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  January 5, 2011 at 12:01 pm

  #1269686

  DECLARE @STR VARCHAR(25)

  SET @STR = '1, 2, 3, 4'

  SELECT STUFF(@STR, (LEN(@STR) + 1 - CHARINDEX(',', REVERSE(@STR))), 1, ' and')

  Here's a good site explaining and demonstrating string functions:

  http://sqlserverpedia.com/wiki/Built-in_Functions_-_String_Functions

  _____________________________________________________________________
  - Nate

  @nate_hughes

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 5, 2011 at 12:02 pm

  #1269687

  select REVERSE(STUFF(REVERSE('1, 2, 3, 4'),CHARINDEX(',',REVERSE('1, 2, 3, 4')),1,'dna '))

  is one way - not a great way - but it is one way....

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]