Post reply

Replace not replacing all instances of text in a string

  • 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.

  • 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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

  • 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.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

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

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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