?
Solved

sql query find and replace

Posted on 2013-02-04
16
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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 1500 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 1500 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
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 1500 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

741 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