Andromedae93 Andromedae93 - 1 month ago 5
MySQL Question

remove first character from mysql field

I need advices in order to make a process on my list of values. I have a table

llx_societe
and some fields where one of them is
code_client
. This field looks like :

0099
00100
00101
00102
...
00998
00999
001000


I want to remove the first zero for all values between
00100
and
00999
in order to get
0100
until
0999
.

I wrote this command :

UPDATE `llx_societe`
SET `code_client`= SUBSTR(code_client,1)
WHERE `code_client` BETWEEN '00100' AND '00999';


But nothing, none lines are proceed.

Have you an explanation ?

Answer

SQL starts counting from 1 and not 0. Try this:

UPDATE `llx_societe` 
SET `code_client`= SUBSTR(code_client,2) 
WHERE `code_client` BETWEEN '00100' AND '00999';