Solved

Trimming a field in SQL

Posted on 2011-02-27
9
346 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
BULK LOGGED - log full 9 25
ODBC Driver 11 for SQL Server]Unable to open BCP host data-file 3 55
email about the whoisactive result 7 27
Replace Dates in query 14 20
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

15 Experts available now in Live!

Get 1:1 Help Now