CODEWITHSUNDEEP
mysql
Essex
Essex

Reputation: 6128

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 ?

Upvotes: 19

Views: 29719

Answers (3)

Wria Mohammed
Wria Mohammed

Reputation: 1611

You can use the following SQL:

UPDATE TABLENAME SET data = SUBSTR(FIELD, 2);

for example if there is table(userinfo) and field is username

UPDATE users SET username = SUBSTR(username, 2);

Upvotes: 2

Mohammad Nurul Huda Rimon
Mohammad Nurul Huda Rimon

Reputation: 2525

Try this:

  UPDATE llx_societe
    SET code_client= SUBSTR(code_client, 2) 
    WHERE code_client between '00100' AND '00999'

MySQL SUBSTR() function

Upvotes: 4

mshaugh
mshaugh

Reputation: 336

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

Upvotes: 32

Related Questions