India's No. #1 Programmer's Forum

How to update and Replace one field First Character Using Mysql

Asked by Sonu Yadav On 21 Mar, 2021 10:07:08pm

I have a table named student, but now I want to replace values in column roll_no

The values are like this:

0850154

0254403

0254144

1236541

8470111

What I want to achieve:

850154

254403

254144

1236541

8470111

Just changing the '0' to ''(Blanck) as first character

Re: How to update and Replace one field First Character Using Mysql

21 Mar, 2021 10:26:57pm

Answer by Chandraket

This above answer is correct and use below code

UPDATE student SET roll_no=CONCAT('', SUBSTRING(roll_no FROM 2)) where left(roll_no,1)='0';

21 Mar, 2021 10:12:16pm

Answer by Expert

Use the below query:

UPDATE student SET roll_no=CONCAT('', SUBSTRING(roll_no FROM 2)) where substring(roll_no,1,1)='0'

UPDATE student SET roll_no=CONCAT('', SUBSTRING(roll_no FROM 2)) where left(roll_no,1)='0';

It will be work fine :)-