SQL Replace Command help required

Question

SQL Replace Command help required

hajduk1908

SSC Veteran

Points: 218
More actions
July 5, 2021 at 4:14 am

#3903443
Hi
I am Part of a Car Club and volunteering to help where I can. The Club has ask me to do some volunteer work on the database, they would like me to go to table and rename all the words "Holden with GM" howver if it reads Holden Elizabeth or Elizabeth Holden not to touch. There are 480 rows in the database and only know the basic sql, still learning it. City is Adelaide. Its a Microsoft SQL Server 2016
The Table is called CARS
The column where these name is called Car Club, this is what it looks like.
Car Club
Holden Seaton - Seaton --------------------------------------- GM Seaton - Seaton
Holden GlenOsmond - GlenOsmond--------------------------GM Glen Osmond - Glen Osmond
PeterHead Holden - Peterhead----------------------------------PeterHead GM - PeterHead
South Side Region Holden - South------------------------------South Side Region GM - South
Elizabeth Holden - Elizabeth---------------------------------Elizabeth Holden - Elizabeth
Holden Elizabeth - Elizabeth South-------------------------Holden Elizabeth - Elizabeth South
This what I have so far
Update Cars
Set Cars Club=Replace(Cars Club,'Holden','GM') and City=Adelaide
where Cars Club!='Elizabeth Holden' or 'Holden Elizabeth'
Not having much luck so far..please help
- This topic was modified 2 years, 11 months ago by hajduk1908.

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

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

Change you where clause to use like and wild cards instead of an equal sign. Also use and instead of or in the where clause:

WHERE [Cars Club] NOT LIKE '%Elizabeth Holdem%' AND [Cars Club] NOT LIKE '%Holdem Elizabeth%'

hajduk1908 SSC Veteran Points: 218 More actions · Answer 2

Thankyou but for some reason still seeing the output as '0' in the column Im trying to change in my test database

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 3

Can you write a small script that creates your table, insert some data and show us the results that you expect to get? It would help us make sure that we are talking about the same thing and anyone that tries to help you would also be able to check his solution

Adi

Sergiy SSC Guru Points: 110207 More actions · Answer 4

Did you correct the spelling mistake in Adi’s script?

Holde[nm]

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110207 More actions · Answer 5

Update Cars

Set Cars Club=Replace(Cars Club,'Holden','GM') and City=Adelaide

where Cars Club like ‘Holden’

and not (Cars Club like '%Elizabeth Holden%' or Cars Club like 'Holden Elizabeth')

_____________
Code for TallyGenerator