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.
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
;
WITH cte AS
(
SELECT input,
Substring(input, 1, Charindex(' ', input)-1) AS firstname,
Substring(input, Charindex(' ', input) + 1, 1000) AS lastname
FROM sample )
SELECT
case
when lastname <> 'Toby' THEN replace (input,'Anne','Marie')
ELSE input
END
FROM cte
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
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
SELECT *,
CASE
WHEN address LIKE '%SWANSEA%'
AND address LIKE '%SCOTLAND%'
AND city = 'LONDON' THEN address
ELSE Replace(address, 'SWANSEA', 'Perth')
END AS ok
FROM sample_data
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?
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
