Solved

Trimming a field in SQL

Posted on 2011-02-27
9
349 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:fbhunt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991562
Try SUBSTRING()
SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1, 10)

Open in new window


Raj
0
 

Author Comment

by:fbhunt
ID: 34991599
I need to assign it a column name ? How ?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991607
Apply SUBSTRING to the column name that you want.
Like
SUBSTRING(YourColumn, 1, 10)

Raj
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:fbhunt
ID: 34991682
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 ?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991718
>> 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

0
 

Author Comment

by:fbhunt
ID: 34991781
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


0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34991793
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
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 50 total points
ID: 34991804
Also ELSE value for the CASE statement is not written?

If that comparion fails that you want to retrieve ?

Updated with 'else'

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

Raj
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34992032
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

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question