Using REPLACE on non-display characters
-
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
-
the brackets INSIDE the quotes means you are looking for the brackets also...that's not what you want.
try this instead:
update address_tableset addr_line_1 = replace(addr_line_1, char(9), ',')where CHARINDEX(char(9),addr_line_1 ) > 0 --if the tab exists anywhere in the stringyour original command really needed to be like this:
update address_tableset 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! -
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