Post reply

Using REPLACE on non-display characters

  • July 7, 2010 at 9:14 am

    #224559

    Hi

    I am having a few challenges using the REPLACE command. Basically I have a column called ADDR_LINE_1 that contains the HOZIONTAL TAB character. As far as I am aware this translates to CHAR(9).

    What I would like to do is to REPLACE the HORIZONTAL TAB with a comma. This what I am doing:

    updateaddress_table

    setaddr_line_1 = replace(addr_line_1, char(9), ',')

    whereaddr_line_1 like '%[char(9)]%'

    Problem is that I get echoed back to the screen that the code has worked but in reality nothing happens.

    For instance, before I run the code the column shows:

    |71, Conner Road |

    And after I run the code it still contains

    |71, Conner Road |

    It is the last and the second last characters after the 'd' in 'Road' that contain the embedded CHAR(9). Visually this appears as a SPACE but after I run my code it still contains CHAR(9).

    I am running SQL Server 2000 – ok ok, it is old, but I don’t control the budget!

    Many thanks

    Ross

  • July 7, 2010 at 9:26 am

    #1189684

    the brackets INSIDE the quotes means you are looking for the brackets also...that's not what you want.

    try this instead:

    update address_table

    set addr_line_1 = replace(addr_line_1, char(9), ',')

    where CHARINDEX(char(9),addr_line_1 ) > 0 --if the tab exists anywhere in the string

    your original command really needed to be like this:

    update address_table

    set addr_line_1 = replace(addr_line_1, char(9), ',')

    where addr_line_1 like '%' + char(9) + '%'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 8, 2010 at 4:11 am

    #1190100

    Hi Lowell

    Thanks, that worked !!

    Cheers

    Ross 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

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