Substituting characters in table

Hello Folks,

I have a column that i would like to update but having some problems..It contains information like the following:

"Brand-name portable power for ipod(r), iphone(r), blackberry(r), android(r) & other usb charged devices"

I want to be able to remove or update the '(r)' and have it replaced with the html code '®' so that the browser will know how to push it to the screen. I`m using the following queries and getting 0 rows when updating..

I run this statement and get results:
SELECT * FROM `tbl_name` WHERE `col_name` Like '%(r)%'  

But when i run the update statement i get 0 rows updated:
update tbl_name set `col_name` = replace(`col_name`,'(r)','®') where `col_name` Like '%(r)%'



Am i missing something here?

Thanks in advance
LVL 11
Eaddy BarnesITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
Is that a typo or you are using ' and ` ? make sure to use ' only?

SELECT * FROM 'tbl_name' WHERE 'col_name' Like '%(r)%'

update tbl_name set 'col_name' = replace('col_name','(r)','®') where 'col_name' Like '%(r)%'
0
Kevin CrossChief Technology OfficerCommented:
` is the proper mark for MySQL column and table names; ' is for string literals.  I tried this and works perfectly fine; therfore, please see what it results in on your system:

SELECT *, REPLACE(col_name, '(r)', '®')
FROM (
   SELECT 'Brand-name portable power for ipod(r), iphone(r), blackberry(r), android(r) & other usb charged devices' AS col_name
   UNION SELECT 'Name with just r'
) tbl_name
WHERE col_name LIKE '%(r)%'
;

Open in new window


My worry originally was that the LIKE expression was seeing the () as part of a regular expression and the REPLACE was treating that differently; however, not sure that is the case or at least the issue is not jumping out at me just yet.  Though, I didn't use quoted identifiers on my column names -- maybe remove those totally -- note using single ' isn't appropriate as that will compare the word 'col_name' as a string literal which is not what you want.
0
Eaddy BarnesITAuthor Commented:
@jimyX I tried this update products set 'name' = replace('name','(r)','®') where 'name' Like '%(r)%'  

and got an error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''name' = replace('name','(r)','®') where 'name' Like '%(r)%'' at line 1

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

schubachCommented:
Your original syntax which I copied directly from your post works fine for me.
mysql> update tbl_name set `col_name` = replace(`col_name`,'(r)','®') where `col_name` Like '%(r)%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Open in new window

Try
select @@sql_mode; 

Open in new window

and tell me what you get.  Maybe it has something to do with how your options are set.  Also is it a MyISAM table or an InnoDB table and what is the data type for col_name?
0
Eaddy BarnesITAuthor Commented:
@ mwvisa1 :  I tried this

SELECT *, REPLACE('name', '(r)', '®')
FROM (
   SELECT 'name' AS col_name
   UNION SELECT 'Name with just r'
) products
WHERE 'name ' LIKE '%(r)%'

It ran and gave "Your SQL query has been executed successfully ( Query took 0.0005 sec )" but nothing got updated..

Am i doing something wrong here guys??
0
Eaddy BarnesITAuthor Commented:
@schubach: give me 5 minutes to get back to you..
0
Eaddy BarnesITAuthor Commented:
@schubach its InnoDB and the column datatype is varchar(250)

select @@sql_mode;
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
0
Kevin CrossChief Technology OfficerCommented:
SELECT *, REPLACE('name', '(r)', '®')
FROM (
   SELECT 'name' AS col_name
   UNION SELECT 'Name with just r'
) products
WHERE 'name ' LIKE '%(r)%'

That is incorrect.  Additionally, this would NOT update anything...it was just a test of the REPLACE function to see if it was working for you and would verify if the quoted identifier ` on the column and table names was the issue.  'name' is a string literal.

SELECT *, REPLACE(col_name, '(r)', '®')
FROM (
   SELECT 'name(r)' AS col_name
   UNION SELECT 'Name with just r'
) products
WHERE col_name LIKE '%(r)%'

The above is what you should try and should result in the one line with (r) in it appearing with an additional column that has 'name®' as its value.  If that doesn't happen, please let us know.
0
Eaddy BarnesITAuthor Commented:
@mwvisa1

SELECT *, REPLACE(col_name, '(r)', '®')
FROM (
   SELECT 'name(r)' AS col_name
   UNION SELECT 'Name with just r'
) products
WHERE col_name LIKE '%(r)%'

This ran successfully but never updated the column..
0
Kevin CrossChief Technology OfficerCommented:
Please post the results you get when you run the above here.  More importantly, please post the ACTUAL original query you were trying here in a CODE block as there may be issues with something in that syntax that is not obvious in what you posted in generic form in the question which executes correctly on the Experts' systems that have posted so far.
0
Eaddy BarnesITAuthor Commented:
Ok Added

The live table_name is products and col_name is 'DESC 2'..
SELECT *, REPLACE('DESC 2', '(r)', '®')
FROM
(
SELECT 'name(r)' AS col_name
UNION SELECT 'Name with just r'
) products
WHERE 'DESC 2' LIKE '%(r)%';

Open in new window

0
Eaddy BarnesITAuthor Commented:
Results i get is:


Your SQL query has been executed successfully ( Query took 0.0006 sec )

SELECT * , REPLACE( 'LONG DESC 2', '(r)', '®' )
FROM (

SELECT 'name(r)' AS col_name
UNION SELECT 'Name with just r'
)products
WHERE 'LONG DESC 2' LIKE '%(r)%'

Open in new window

0
Eaddy BarnesITAuthor Commented:
Sorry i had an error is last query, the col_name should be 'DESC 2'..
0
schubachCommented:
Please post your original update statement.  Could you also run:
mysqldump -database table 

Open in new window

where database is your actual database name and table is your actual table name and post the output.  That will give your exact table definition and data.  If there are lots of rows, cut out most of the data rows just leaving one example row to show us what is going on.
0
schubachCommented:
Sorry, that should have said
mysqldump database table

Open in new window

the dash should not be there.
0
schubachCommented:
When you use single quotes, it is not querying your actual tables, but rather just manipulating string literals.  So you need to use backticks around your actual table and column names like in your original post, or optionally don't quote them at all if they are just plain strings.
0
Kevin CrossChief Technology OfficerCommented:
http:#a35301523 << Exactly.  This is what I was trying to say earlier.  Thanks!

Run this:
SELECT `LONG DESC 2` , REPLACE( `LONG DESC 2`, '(r)', '&#174;' ) AS DescReplaced
FROM products
WHERE `LONG DESC 2` LIKE '%(r)%';

Open in new window


It will NOT update your original table, it will only display the record values with an additional column called DescReplaced.  When I say post results I don't want the time it took to run, but the actual resultset or small sampling of it.

Therefore, past the result of the first couple of rows.

Once we know that is working for you correctly, then you can turn this into an UPDATE like so:
UPDATE products
SET `LONG DESC 2` = REPLACE( `LONG DESC 2`, '(r)', '&#174;' ) 
WHERE `LONG DESC 2` LIKE '%(r)%';

Open in new window


Kevin
0
Eaddy BarnesITAuthor Commented:
Guys i`m going to post back later leaving for work now..
0
Eaddy BarnesITAuthor Commented:
@mwvisa1 I ran the below query and got the results which i attached sample.xls ..
SELECT `LONG DESC 2` , REPLACE( `LONG DESC 2`, '(r)', '&#174;' ) AS DescReplaced
FROM products
WHERE `LONG DESC 2` LIKE '%(r)%';

Open in new window

sample.xls
0
Eaddy BarnesITAuthor Commented:
Now when I run the second query it comes up empty nothing gets updated.. What it says is 0 row(s) affected. (Query took 0.0552 sec) this is all i get back..
UPDATE products
SET `LONG DESC 2` = REPLACE( `LONG DESC 2`, '(r)', '&#174;' ) 
WHERE `LONG DESC 2` LIKE '%(r)%';

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Very strange.  Did you ever answer on what the data type of the column LONG DESC 2 is?
What I notice from the results is that the REPLACE() didn't actually work even in the SELECT.  The oddity is that you get 0 rows updated on the UPDATE as it should have updated same number of rows you selected, but they would have had the same value as what they started with as the replace isn't actually working.  
0
Eaddy BarnesITAuthor Commented:
yep the datatype is varchar(250)
0
Eaddy BarnesITAuthor Commented:
same thing i'm saying, using the Like to create another column  works but using it to update just fizzles out, lol..
0
schubachCommented:
OK, now that you posted that Excel output, I'm finally able to replicate your problem.  Change your update statement to:
UPDATE products
SET `LONG DESC 2` = REPLACE( `LONG DESC 2`, '(R)', '&#174;' ) 
WHERE `LONG DESC 2` LIKE '%(R)%';

Open in new window

Please note in the documentation at
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_replace
it says
REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

Also note this output
mysql> select * from tbl_name where col_name like '%(R)%';
+----------+
| col_name |
+----------+
| (r)      |
| (R)      |
+----------+
2 rows in set (0.00 sec)

mysql> UPDATE tbl_name set `col_name` = replace(`col_name`,'(R)','&#174;') where `col_name` like '%(R)%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

Open in new window

Note that in the above output, both rows match because the where clause is comparing case insensitive, but the replace statement is case-sensitive, so only 1 row is changed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eaddy BarnesITAuthor Commented:
Well DONE..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.