SQLTeam.com | Weblogs | Forums

SQL Replace problem


I am trying to figure an sql command where the query will replace a keyword but not if it finds a certain string.
For example Replace Word 'Anne' with "Marie' so it become Marie
Input Anne Jones will become Marie Jones in field however if it finds Anne Toby ignore it and move to the next field, so the column name will look like this, we can call it the first name column

Input (First Name Column)
Anne Jones
Anne Roger
Anne Peterson
Anne Brown
Anne Toby Output Marie Jones
Marie Roger
Marie Peterson
Marie Brown
Anne Toby (dont want to change)

Have attempted the select replace command ('Anne','Anne','Marie') and Update Table and set Input Column Name(First Name) but it does not ignore the Anne Toby string for some reason.


hi hope this helps

please click arrow to the left for DROP CREATE Sample Data
create table Sample
Input varchar(100)

insert into Sample select 'Anne Jones'
insert into Sample select 'Anne Roger'
insert into Sample select 'Anne Peterson'
insert into Sample select 'Anne Brown'
insert into Sample select 'Anne Toby'
insert into Sample select 'Marie Roger'
insert into Sample select 'Marie Peterson'
insert into Sample select 'Marie Brown'

select 'Sample Data',* from Sample
       SELECT input,
              Substring(input, 1, Charindex(' ', input)-1)      AS firstname,
              Substring(input, Charindex(' ', input) + 1, 1000) AS lastname
       FROM   sample )
              when lastname <> 'Toby' THEN replace (input,'Anne','Marie')
              ELSE input
FROM   cte


1 Like

Thankyou, will have a look and test as soon as I can.

Hi...This doesnt seem to be working

could you please explain ?

doing it looks really easy ..

the only hard part is understanding WHat you are looking for ?

1 Like

Please excuse the differnt wording

Here is test table, I would like to change word Swansea to Perth in an existing table unless it sees Swansea Scotland or Scotland Swansea if the city = london....disregard all other cities

Hi again, it will need to ignore Swansea Scotland or Scotland Swansea...I would do it manually but there are 460 rows

its really easy to do

please give me 1 hour

i am in the middle of some other things

1 Like

no problems, I still learning my way around SQL

its a HUGE LEARNING curve

but there are lot of ways to cut it short drastically i mean super drastically

1 Like
create Table Sample_data
Address varchar(100),
City varchar(100)

insert into sample_data select 'BUCSIA SWANSEA - BUCASIA','London'
insert into sample_data select 'SWANSEA SCOTLAND - AITKENVALE','London'
insert into sample_data select 'SCOTLAND SWANSEA - Erindale','London'

insert into sample_data select 'BUCSIA SWANSEA - BUCASIA','New York'
insert into sample_data select 'SWANSEA SCOTLAND - AITKENVALE','New York'
insert into sample_data select 'SCOTLAND SWANSEA - Erindale','New York'

select 'Sample Data',* from sample_data

         WHEN address LIKE '%SWANSEA%'
              AND address LIKE '%SCOTLAND%'
              AND city = 'LONDON' THEN address
         ELSE Replace(address, 'SWANSEA', 'Perth')
       END AS ok
FROM   sample_data 

1 Like

Thanks again...what if I did not want to create the extra column ok,,,just make the changes to the address column in table and noo additianal coulumns in that table...is that possible?

i think I have may have worked it out :grinning:



Hi Harishgg1
I did some further testing today, the query ran successfully took about 15 mins over 450 rows however when I did the select sql statement it, it was still displaying SWANSEA on many of the addresses instead of Perth....Im Confused

Unless the field is write protected somehow?....seems to work ok W3C school

15 minutes for 450 rows ( something looks fishy here ) ... 450 rows should be done in 2 seconds


i am adding the city = 'LONDON' clause

-- that your data might be differerent .. please see your data

SWANSEA i am checking upper case the data might be lower case swansea