Replace not replacing all instances of text in a string

Question

Post reply

Replace not replacing all instances of text in a string

richardmgreen1

SSChampion

Points: 10305
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.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246711 More actions · Answer 1

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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

richardmgreen1 SSChampion Points: 10305 More actions · Answer 2

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: 246711 More actions · Answer 3

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

richardmgreen1 SSChampion Points: 10305 More actions · Answer 4

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: 246711 More actions · Answer 5

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','')

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.