Solved

sql query find and replace

Posted on 2013-02-04
16
234 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 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