What symbol to use instead?

Dev Shed Forums Sponsor:

October 19th, 2012, 08:34 AM

leozx9r

Registered User

Join Date: Oct 2012

Posts: 3

Time spent in forums: 27 m 28 sec
Reputation Power: 0

What symbol to use instead?

Hello Guys,
I hope I can make sense and get an answer..
I'm trying to update a field on a table..
This field contains different forwarding methods for a message.
For Example: EIioT which stands for Email Immediately incoming outgoing T (not sure)
If I want to change for only incoming I can change it to: EIiT but this way, if the first letter was S (for SMS) will default all to E (email).
How can I just modify/add/remove a letter in the middle without effecting others?
I tried * and ?..

Hope yopu can help..

Regards to all

October 19th, 2012, 10:42 AM

Problem Solver

Join Date: Jan 2001

Location: Stockholm, Sweden

Posts: 4,430

Time spent in forums: 3 Weeks 4 Days 7 h 23 m 50 sec
Reputation Power: 532

String Replace():

Code:

UPDATE ...
SET
  yourColumn = REPLACE(yourColumn, 'x', 'y')
WHERE
  yourColumn LIKE '%x%'

The WHERE LIKE is to explicitly choose the rows that need to be updated.

__________________
/Stefan

October 22nd, 2012, 05:09 AM

leozx9r

Registered User

Join Date: Oct 2012

Posts: 3

Time spent in forums: 27 m 28 sec
Reputation Power: 0

Thank you Stephan..

can't get it though..

I used:

Set FORWARD_DETAILS = '%IioT'

out of all the above letters I want only the first not to be touched...

do I need the % sign at the end too?

October 22nd, 2012, 05:18 AM

leozx9r

Registered User

Join Date: Oct 2012

Posts: 3

Time spent in forums: 27 m 28 sec
Reputation Power: 0

This is what I wrote:

update MESSAGE_ACTIONS
SET
FORWARD_DETAILS = REPLACE ( FORWARD_DETAILS ' i ' , ' io ' )
WHERE
FORWARD_DETAILS LIKE ' %i% '

not working

October 22nd, 2012, 07:25 AM

Problem Solver

Join Date: Jan 2001

Location: Stockholm, Sweden

Posts: 4,430

Time spent in forums: 3 Weeks 4 Days 7 h 23 m 50 sec
Reputation Power: 532

And what is the error message that you get?

Could it be:

Code:

mysql> update MESSAGE_ACTIONS
    -> SET
    -> FORWARD_DETAILS = REPLACE ( FORWARD_DETAILS ' i ' , ' io ' )
    -> WHERE
    -> FORWARD_DETAILS LIKE ' %i% ';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv
er version for the right syntax to use near '' i ' , ' io ' )
WHERE
FORWARD_DETAILS LIKE ' %i% '' at line 3

And by looking and stepping through this code I can see that:
1. You don't have a comma between the column name FORWARD_DETAILS and the 'i'.
2. You have spaces between the ' sign and the letter i and the letters io, which means that mysql is right now trying to replace the string '(space)i(space)' with the string '(space)io(space).
3. You have spaces inside the string in LIKE also.

So try this instead:

Code:

update MESSAGE_ACTIONS
SET
FORWARD_DETAILS = REPLACE ( FORWARD_DETAILS, 'i' , 'io' )
WHERE
FORWARD_DETAILS LIKE '%i%'

What symbol to use instead?

Developer Shed Advertisers and Affiliates