Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trimming a field in SQL

Posted on 2011-02-27
9
Medium Priority
?
355 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 200 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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