Trim the leading characters of a field

Hi,

A newbie to php and mySql so please bear with me.

I have imported some data into a table but the first field has a leading "R
" which I need to trim off all 4,000 records.

Typically the field content is "R ERR3340" and I need to modify this value
to be "ERR3340"

I have tried


UPDATE part_num,
LTRIM( part_num, "R " ) FROM `source_premier`


to no avail

TIA
Ian

Re: Trim the leading characters of a field

Just a thought - why not find/replace "R ERR"/"ERR" prior to importing


Ian Robinson wrote:
> Hi,
>
> A newbie to php and mySql so please bear with me.
>
> I have imported some data into a table but the first field has a leading "R
> " which I need to trim off all 4,000 records.
>
> Typically the field content is "R ERR3340" and I need to modify this value
> to be "ERR3340"
>
> I have tried
>
>
> UPDATE part_num,
> LTRIM( part_num, "R " ) FROM `source_premier`
>
>
> to no avail
>
> TIA
> Ian

Re: Trim the leading characters of a field

Yes, that's a sort of option except that all the records in this field
contain a variety of chars other than "ERR******". I could find/replace "R
A"/"A", then "B" and so on. Cannot simply find/replace "R "/"" since I would
loose values from other fields such as "DEFENDER 90"/"DEFENDE90".

I am moving a desktop application written in delphi and using paradox tables
to the web with PHP and MySql. I have all the data manipluation routines
written for Delphi but I would like to import directly into MySql from the
file I'm supplied with. I'm supplied with 4 others on a monthly basis and
manually tidying them all is an additional chore. Since 'LTRIM' is an SQL
function I *should* be able to get it to work and if I can then on going
admin when the application is web based will be better facilitated.

Thanks for your input
Ian


"strawberry" wrote in message
news:1142523595.980637.80480@v46g2000cwv.googlegroups.com...
> Just a thought - why not find/replace "R ERR"/"ERR" prior to importing
>
>
> Ian Robinson wrote:
>> Hi,
>>
>> A newbie to php and mySql so please bear with me.
>>
>> I have imported some data into a table but the first field has a leading
>> "R
>> " which I need to trim off all 4,000 records.
>>
>> Typically the field content is "R ERR3340" and I need to modify this
>> value
>> to be "ERR3340"
>>
>> I have tried
>>
>>
>> UPDATE part_num,
>> LTRIM( part_num, "R " ) FROM `source_premier`
>>
>>
>> to no avail
>>
>> TIA
>> Ian
>

Re: Trim the leading characters of a field

So you basically want to remove the first two characters in every row
in that column right? If so, I think what you want is this - but it
might not be right so IT IS VITAL THAT YOU BACK UP YOUR DATA BEFORE
DOING THIS!!!! YOU HAVE BEEN WARNED...

UPDATE table_name SET field_name = SUBSTRING(field_name_name,2)

Re: Trim the leading characters of a field

Sorry, I meant

UPDATE table_name SET field_name = SUBSTRING(field_name,2)

Re: Trim the leading characters of a field

Zac,

Perfect, thanks very much



"strawberry" wrote in message
news:1142557832.505582.160100@v46g2000cwv.googlegroups.com.. .
> Sorry, I meant
>
> UPDATE table_name SET field_name = SUBSTRING(field_name,2)
>

Re: Trim the leading characters of a field

Sorry to bother you again, but just so I understand this properly how would
I remove spaces from within a field which could be at random positions
within that field ?

i.e
R ERR3340
R DA 3025
STC345 AG

TIA
Ian

>
> UPDATE table_name SET field_name = SUBSTRING(field_name,2)
>

Re: Trim the leading characters of a field

There may be a simpler way but this should work...

UPDATE table_name SET field_name = replace(field_name, ' ', '')

Re: Trim the leading characters of a field

Thank you

Regards
Ian


"strawberry" wrote in message
news:1142603780.626139.12240@e56g2000cwe.googlegroups.com...
> There may be a simpler way but this should work...
>
> UPDATE table_name SET field_name = replace(field_name, ' ', '')
>