Replace not replacing all instances of text in a string

• richardmgreen1

  SSChampion

  Points: 10384

  More actions

  November 21, 2014 at 6:11 am

  #151387

  Hi all

  I've got some string fields that may/may not have the following at the beginning:-

  Verdana4d

  

  The above is in one line in the relevant field.

  I want to remove all instances of this particular piece of text (it looks to me like some sort of formatting code) so I'm using the following:-

  replace(Value,'Verdana4d

  

  ','')

  but it's not collecting all the instances is each record.

  When I paste the text into the SQL GUI (which we use for writing code), it comes out without the arrow or the bullet (obviously the arrow and the bullt aren't recognised by the GUI) but it works on some records but not others.

  At the moment, I'm having the nest the replace statements to remove them all but I just wondered if anyone knew why it's not picking up all instances?

  Any help greatly appreciated.

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  November 21, 2014 at 6:17 am

  #1760091

  Are you able to view the data in something like Notepad ++ and do View / Show Symbol / Show all characters

  just to confirm that there are no tabs, double spaces or other weird characters in there?

  ---

• richardmgreen1

  SSChampion

  Points: 10384

  More actions

  November 21, 2014 at 6:23 am

  #1760093

  Never thought of that.

  This is what I see in Notepad++

  Verdana4d

  In Notepad++ it appears as:-

  CAN (in bold) followed by "Verdana" then a right-arrow (in orange, bizarrely) then "4d" then CR and LF (both in bold)

  On line 2, I can see BEL (in bold)

  All the items in bold have a black background with white text.

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  November 21, 2014 at 6:33 am

  #1760096

  I think that the 'right arrow' is a tab.

  So if you replace tabs with spaces before doing your verdana replace, you should get more hits.

  ---

• richardmgreen1

  SSChampion

  Points: 10384

  More actions

  November 21, 2014 at 6:42 am

  #1760100

  Thanks for that.

  I'm currently looking at several repalce statements to remove the text in pieces rather than all at once.

  I'll let you know how that goes.

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  November 21, 2014 at 6:48 am

  #1760104

  richardmgreen1 (11/21/2014)

  ---

  Thanks for that.

  I'm currently looking at several repalce statements to remove the text in pieces rather than all at once.

  I'll let you know how that goes.

  A nested REPLACE should do it all in one go. This sort of thing:

  replace(replace(replace(expr, tab, space), double space, single space),'Verdana 4d','')

  ---