sql query find and replace

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
JCWEBHOSTAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
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
 
Lee SavidgeCommented:
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
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
something like:

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

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
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
 
JCWEBHOSTAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
JCWEBHOSTAuthor Commented:
ok thanks  jyparask that works fine, but now how do i do i delete?
0
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
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
 
JCWEBHOSTAuthor Commented:
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
 
JCWEBHOSTAuthor Commented:
no for the delete
0
 
Ioannis ParaskevopoulosCommented:
I am sorry but i don't get what is your problem. Can you explain further?
0
 
JCWEBHOSTAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
Would it be easier to recreate the comma separated list and update the whole field;
0
 
JCWEBHOSTAuthor Commented:
you want me to replace to numbers?
0
 
Ioannis ParaskevopoulosCommented:
Are you using the three table logic;
0
 
JCWEBHOSTAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.