Mysql Updating table With Substring

Question

i have table name master and column name with number

column number have value = A011017

I want change 5=0 change with 2

i have try with this code

update master set number =substr(number,5,1) where number like 'A011%'

I want change all rows with 2 in the 5th position

Help me please

"column Number have value = A011017 I want change 5=0 change with 2"...please explain it clearly. — uvais
– uvais, Commented Dec 3, 2013 at 11:03
i mean record number 5 from A011017 is zero (0) i want change value with 2 — user3061075
– user3061075, Commented Dec 3, 2013 at 11:05
for all records whose start vith A011 or only for this one? — Mikhail Timofeev
– Mikhail Timofeev, Commented Dec 3, 2013 at 11:07

Tom Mac · Accepted Answer · 2013-12-03 12:10:36Z

2

Something like this?

update master 
set number =
concat(substring(number,1,4),"2",substring(number,6))
where number like 'A0110%'
;

You can adjust the where clause accordingly (not sure whether you want all rows with 0 in the 5th position or just those rows starting with 'A011'). For example, if you want to change all rows with '0' in the 5th position to have '2' in the 5th position then use this:

update master 
set number =
concat(substring(number,1,4),"2",substring(number,6))
where substring(number,5,1) = "0"
;

...or if you want to change all rows with '2' in the 5th position to have '0' in the 5th position then use this:

update master 
set number =
concat(substring(number,1,4),"0",substring(number,6))
where substring(number,5,1) = "2"
;

edited Dec 3, 2013 at 12:10

answered Dec 3, 2013 at 11:06

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3061075 Over a year ago

I want change all rows with 2 in the 5th position

user3061075 Over a year ago

I want change all rows with 2 in the 5th position

Collectives™ on Stack Overflow

Mysql Updating table With Substring

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related