Solved

Trimming a field in SQL

Posted on 2011-02-27
9
344 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now