CODEWITHSUNDEEP
mysql
user3061075
user3061075

Reputation: 11

Mysql Updating table With Substring

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

Upvotes: 1

Views: 86

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

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

Upvotes: 2

Related Questions