asked on

Trimming a field in SQL

Can I trim a field down to 10 characters in SQL 2008.
LTRIM and RTRIM only remove the blanks.
Left does not seem to work, when I key it in a query that word left remains gray and does nothing.

I have a string of 256 characters, All I want is the left 10 characters.

Rajkumar Gs

Try SUBSTRING()

SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1, 10)

Open in new window

Raj

fbhunt

ASKER

I need to assign it a column name ? How ?

Rajkumar Gs

Apply SUBSTRING to the column name that you want.
Like
SUBSTRING(YourColumn, 1, 10)

Raj

fbhunt

ASKER

Not understaning what you mean.

I have a column in a view that is labeled new_value (this has a length of up to 255 chars)
I want the leftmost positions to use for testing and creating a new column.

Example : new_value = 'acct123456 transfer numter'
I want the acct12456 put to a new column so i can test the 10 chars.
so I can test as follows:

when short_value = 'acct12456' then 1001

substring(new_value,1,10) as short_value <--- IS this correct ?

Rajkumar Gs

>> substring(new_value,1,10) as short_value <--- IS this correct ?
yes. that's what I mean

Test whether you are getting the trimmed data in this manner.
Raj

fbhunt

ASKER

Select new_value,
substring(new_value,1,10) as short_value <--- this statement gives me an error
case
when short_value = 'acct123456' then 1001
as salesman_ID
from view_audit_trail_customer_1307

Rajkumar Gs

Corrected Query

Select new_value,
substring(new_value,1,10) as short_value
, case
when substring(new_value,1,10) = 'acct123456' then 1001
end salesman_ID
from view_audit_trail_customer_1307

Let me know
Raj

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

you can't make a new expression and name it and then use the expression name
at the same level in sql ...

you can make an expression at a lower level and then use it in the "outer" layer

select myvar ...
from ( select myvar=substring(columnname,1,10)
from ...
) as mytable
...

also

you can have a case statement of the format

case Expression
when Value (or Expression) but only for an EQUALS TEST
when Value (or Expression)
...
else
end

but you can;t mix the two formats in one ,.... just NEST them...

Select New_Value
      ,short_value
      ,case short_value
            when 'acct123456' then '1001'
            when 'acct234567' then '1002' 
            ...
            else 'Other'
            end as Coded
From (
Select x.*,new_value,
substring(new_value,1,10) as short_value          <--- this statement gives me an error
from view_audit_trail_customer_1307
) as X

case
when short_value = 'acct123456'  then 1001
as salesman_ID

Open in new window