Solved

sql query find and replace

Posted on 2013-02-04
16
233 Views
Last Modified: 2013-03-01
hey guys i need to find a replace in a coloum of my products table.

my coloum is tags  and the text value is:

mig torches,mig welding machines,welding

now i need to update "mig torches" text only

i need to search for words in inside the  comma ","

so if i have to update mig welding to mig w, it must not update  mig torches becouse of the word mig in it
0
Comment
Question by:JCWEBHOST
16 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38850402
Do you mean:

update myTable set myColumn = replace(myColumn, 'mig welding', 'mig w')

If not can you give a few examples of what you have and then show what you are hoping to acheive?
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 125 total points
ID: 38850496
something like:

UPDATE products 
SET tags  = replace(tags, 'mig welding','mig w')

Open in new window

0
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 375 total points
ID: 38850518
Hi,

The following example will only replace the rows that TAGS contain 'mig torches'

DECLARE @ValueNeedsReplace AS VARCHAR(100)
DECLARE @NewValue AS VARCHAR(100)

SET		@ValueNeedsReplace = 'mig torches'
SET		@NewValue = 'mig t'

UPDATE PRODUCTS
SET	TAGS = REPLACE(TAGS,@ValueNeedsReplace,@NewValue)

Open in new window


Giannis
0
 

Author Comment

by:JCWEBHOST
ID: 38850546
can i explain in a easy way

i have a table called tags

here is the column names

id            name          

1             mig torches
2             mig welding machines

now i have a products table

and here are a few columns

id           name             price           tags


the tags colums store tags the users selects separated by a commas


like this   mig torches,mig welding machines,welding

now i am updating my tags table and i also need to update the tags the the product table
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38850565
In your products table in the column tags do you use the id of the tags table or the name?

Also, it would be a better idea to have an extra table like ProductTags that could be the following

ProductId           TagId

So a product with id 5 that has both mig torches and mig welding machines would have two rows in that table

ProductId   TagID
---------------    --------
5                 1
5                 2

This way you would only insert and delete rows in the ProductTags.

Giannis
0
 

Author Comment

by:JCWEBHOST
ID: 38850572
ok thanks  jyparask that works fine, but now how do i do i delete?
0
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 375 total points
ID: 38850580
Hi,

DECLARE @YourDeletedTag int
SET @YourDeletedTag = 1

DELETE FROM ProductTag WHERE TagID = @YourDeletedTag

Open in new window


after this your ProductTag table would be

ProductId   TagID
---------------    --------
5                 2

Thanks,
Giannis
0
 

Author Comment

by:JCWEBHOST
ID: 38867196
this update statement is correct but i want a delete

UPDATE PRODUCTS
SET	TAGS = REPLACE(TAGS,@ValueNeedsReplace,@NewValue) 

Open in new window


and can replace my tag name with an empty string but the problem is the comma

what if they is no comma ?

how do i replace

mig torches,mig welding machines,welding

mig torches or welding?

the comma is the issue
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 375 total points
ID: 38867238
Doyou meanthat after your update with an empty string you will have two commas; then you may do the following:

UPDATE PRODUCTS
SET      TAGS = REPLACE(REPLACE(TAGS,@ValueNeedsReplace,@NewValue) ,',,',',')

This way if @NewValue is an empty string it will output only one comma.

Giannis
0
 

Author Comment

by:JCWEBHOST
ID: 38867271
no for the delete
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38867358
I am sorry but i don't get what is your problem. Can you explain further?
0
 

Author Comment

by:JCWEBHOST
ID: 38867450
i am doing a delete so if this was in my colomn value

mig torches,mig welding machines,welding

how with i remove  mig welding machines?

or

welding

you see mig welding machines, has a comma but welding does not have a comma?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38867560
Would it be easier to recreate the comma separated list and update the whole field;
0
 

Author Comment

by:JCWEBHOST
ID: 38867620
you want me to replace to numbers?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38867708
Are you using the three table logic;
0
 

Author Closing Comment

by:JCWEBHOST
ID: 38941955
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now