Solved

Trimming a field in SQL

Posted on 2011-02-27
9
348 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

766 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