Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql query find and replace

Posted on 2013-02-04
16
Medium Priority
?
244 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:Meir Rivkin
Meir Rivkin 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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