Trim question & IF question

[hc1619]

Hi there. I need to do something which I would usually do in ColdFusion but this thing in particular I HAVE to do within the SQL.. and I'm not sure how.

First is a Trim. I have a char field called Nickname... and I need to have it trimmed of the whitespace when it comes out of the stored proc. I thought it would be something like "Trim(Nickname)", but that didn't work . Can someone help me out here please?

Secondly... a bit more complicated.. I have a field called "mlevel", which is either 1 or 2. But I need to bring it out of the stored proc as 0 or 1.. so if its 1, its 0, and 2 its 1. I need do it in a single line...

I've been told this is how it would be done in MySQL:
"if(mlevel=2,1,0)". How would this be done in SQL?

Thanks people!

 

[mrdenny]

SQL doesn't have TRIM. It has LTRIM and RTRIM instead. Same idea though. RTRIM('sadf ') would return sadf.

Changing the 1,2 to 0,1 could be done in 2 ways. The first is a case statement.

select case when Column = 1 then 0 else 1 end
from Table

The second is to use subtraction.

select Column-1
from table

Denny

--Anything is possible. All it takes is a little research. (Me)