LTRIM UPDATE does not work 2

[cb49747]

I'm running this query

UPDATE loans SET altloannumber = LTRIM( altloannumber, '0' ) WHERE investornumber =323;

the field altloannumber is a varchar field.

I get this error.

Error
SQL query:

UPDATE loaninfo SET altloannumber = LTRIM( altloannumber, '0' ) WHERE investornumber =323;



MySQL said:

#1064 - You have an error in your SQL syntax near ''0')WHERE investornumber=323' at line 2

Any ideas?

 

[vacunita]

[blue]ltrim()[/blue] only takes one paramater: the string from which to remove leading spaces.

What is it you want the [red]"0"[/red] to do?

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[cb49747]

I was hoping to remove all the leading zeros from the field altloannumber

 

[cb49747]

the number of leading zeros vary for different records

 

[vacunita]

IF your field altloannumber always represents numbers, with no decimal points you can use [blue]CAST()[/blue].

UPDATE loaninfo SET altloannumber = CAST( altloannumber as UNSIGNED ) WHERE investornumber =323;

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[cb49747]

It doesn't. Some records can have a alpha char in this field. However all the records effected with this query would have only numbers in the altloannumber field.

So my question would be. Would the above query only effect those records?

 

[feherke]

Hi

[blue]mysql>[/blue] [b]select[/b] trim(leading [i]'0'[/i] from [i]'001a2b'[/i]);
+---------------------------------+
| trim(leading '0' from '001a2b') |
+---------------------------------+
| 1a2b                            |
+---------------------------------+
1 row in set (0.03 sec)

Feherke.

http://rootshell.be/~feherke/

 

[cb49747]

feherke,

Thanks a bunch.

I used the following query and it worked great.

UPDATE `loaninfo` SET `altloannumber`=TRIM(leading '0' from `altloannumber`) WHERE `investornumber` LIKE '323';

vacunita thanks for your help as well.