substring question

[ITbeast]

I have a query that works fine for my initial database population, but I need another one for after that point. Basically, I have a field that comes out as DOMAIN1+username but all I want is the user name. So I came up with this mysql statement that works OK:

update logfile set rfc931 = substring(rfc931,9,64) where rfc931 is not null;

But now I want to only modify fields that only have the DOMAIN+username value, not the already stripped down field of just username. I've tried several if then statements but none seem to come out properly. My last try was:

if substring(rfc931,8,1) = '+' then update logfile set rfc931 = substring(rfc931,9,64) where rfc931 is not null;


This gives errors also. I can't find a good resource on the internet to give me examples of syntax. Any help would be appericated!

 

[ethorn10]

What about:

update logfile
set rfc931 = substring(rfc931,9,64) 
where rfc931 is not null
   and substring(rfc931,8,1) = '+'

[red]not tested so no warranties on your data after you try that[/red]

 

[ITbeast]

That did the trick, thanks for the help. Sorry if it was a really easy question but I'm not a SQL guy I'm just trying to get this one application working.

 

[ethorn10]

Not a problem. Glad it worked.