?
Solved

SQL Statement To Trim Characters

Posted on 2003-03-07
28
Medium Priority
?
634 Views
Last Modified: 2008-03-10
I am looking for the sql statement that will trim the last 3 characters from an string. i.e - abcdefg would be, abcd.  
0
Comment
Question by:rrdiii2003
[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
  • 11
  • 6
  • 4
  • +3
28 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8090927
SELECT LEFT(string, LEN(string) - 3)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8090941
Oops, MAY need to verify the length first, depending on your data:

SELECT CASE WHEN LEN(string) >= 3 THEN LEFT(string, LEN(string) - 3) ELSE string END AS string
--or, if you prefer an empty string if less than 3,
--naturally use "ELSE ''" instead of "ELSE string"
0
 

Author Comment

by:rrdiii2003
ID: 8091026
I get a invalid length param when I use the select left.  The size of the field is 60 and the data in the field ranges.  On each field the last three char are all the same and I just want to drop them...
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.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8091047
Were you also able to try the second version, with the CASE conditions?
0
 

Author Comment

by:rrdiii2003
ID: 8091083
Yes I cant get that to run, still trying...
0
 
LVL 7

Expert Comment

by:TroyK
ID: 8091094
rdiii;

> On each field the last three char are all the same and I just want to drop them...

CREATE TABLE Test(
  Thing varchar(60)
)
GO

INSERT INTO Test
SELECT 'Somthing here ABC' UNION ALL
SELECT 'Something else here ABC' UNION ALL
SELECT 'Yet another thing ABC'
GO

SELECT REPLACE(Thing, 'ABC', '')
FROM Test
GO

DROP TABLE Test
GO

HTH,
TroyK, MCSD
0
 

Author Comment

by:rrdiii2003
ID: 8091142
Troy,
This is a query, I don't want to make changes to the db.  Would that statement make changes?  I am only looking for a statement to make the data look different on the result set within the query result itself.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8091158
I tried it on several tables and didn't have any problem with my CASE-based query.

What error do you get when you try this:


SELECT CASE WHEN LEN(columnName) >= 3
    THEN LEFT(columnName, LEN(columnName) - 3)
    ELSE columnName END AS columnName
FROM tableName
0
 

Author Comment

by:rrdiii2003
ID: 8091178
Scott,

Here it is:  Invalid length parameter passesd to the substring function.

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8091490
I don't get it.  Can you post the actual code you're running?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8091511
And the exact datatype of the column.
0
 
LVL 7

Expert Comment

by:TroyK
ID: 8091638
rrdiii;

Since it's a SELECT statement (and not an UPDATE), no data will change in your table if you use the REPLACE(...) function. Same with the LEFT(...) solution that Scott proposes.

HTH,
TroyK, MCSD
0
 

Expert Comment

by:jddphd
ID: 8091714
here you go:

left(field,len(rtrim(field)-3)

this first trims off any extra space from the data in the field, then figures out how long it is and subtracts three)

should work like a charm

happy hunting
-- J.D.

0
 

Expert Comment

by:jddphd
ID: 8091720
oops - typo!

left(field,len(rtrim(field))-3)

where field is corresponds to your field
0
 

Author Comment

by:rrdiii2003
ID: 8094054
Scott,

When I get back to my computer, I will get exact datatype of the column and code.

Thank You for your help...
0
 

Author Comment

by:rrdiii2003
ID: 8094058
JD,

Thanks!  I will give that a try, to see if it will work for me.
0
 

Expert Comment

by:jddphd
ID: 8094176
another observation:

theoretically, scottpletcher's statement should have worked, but if you are getting an invalid length, perhaps it is because of null values?

if so, you need to evaluate the field for null. best way to do it would be the case statement:

case
when field is not null then left(field,len(rtrim(field))-3)
else whateveryouwanttodohere
end

you can actually omit the else clause to return null values for the case statement.

you could also, as scottpletcher suggests, use the case statement to evaluate the length of the field if you want to do something for strings that are <= 3 chars.

case
when field is not null and len(field)>3 then left(field,len(rtrim(field))-3)
when field is not null and len(field)<=3 then dosomething
else whateveryouwanttodohere
end
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8096028
The problem is you don't have a name for the field

SELECT LEFT(string, LEN(string) - 3) as Field1

mlmcc
0
 

Author Comment

by:rrdiii2003
ID: 8097510
mlmmc,

Would the name or (field1) be an alais ?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8097633
Field1 can  be any name you want.  I generally do something like

SELECT LEFT(txtLastName, LEN(txtLastName) - 3) as LastName

mlmcc
0
 

Expert Comment

by:jddphd
ID: 8102815
rrdiii2003 -

no offense to mlmcc, but unless you are trying to something very out of the ordinary, this suggestion is about not having a field name is incorrect.

a field name for a computed field is unnecessary in query analyzer, or in the MS access SQL pass-through mode, or in pretty much every other place. it will default to blanks, or in the case of access, Expr1.
0
 

Author Comment

by:rrdiii2003
ID: 8103246
Scott the field is char 60
0
 

Author Comment

by:rrdiii2003
ID: 8103676
This field is an email address can I just take everything from the @ to the right off?
0
 
LVL 1

Expert Comment

by:malekam
ID: 8103846
Your original error statement occurs because the length of the column data is less than 3.
If the value is NULL then the functions will return NULL and not produce an error.
Try running these two statements to make sure that neither produce errors

1. SELECT LEN(col_name) FROM tbl_name
2. SELECT LEFT(col_name, LEN(col_name) - 3)
   FROM tbl_name
   WHERE LEN(col_name) > 3

If either two of these queries error out, based on the error messages, we can limit the amount of things that can be going wrong.
0
 

Author Comment

by:rrdiii2003
ID: 8104279
Bingo malekam! No errors !!! That worked, now all that I have to do is put this into a case statement.....any ideas?
0
 
LVL 1

Expert Comment

by:malekam
ID: 8104802
This will remove everything from the @ to the right:

SELECT CASE CHARINDEX('@', col_name)
          WHEN 0 THEN col_name
          ELSE LEFT(col_name, CHARINDEX('@', col_name))
       END AS col_name
FROM tbl_name
0
 
LVL 1

Accepted Solution

by:
malekam earned 200 total points
ID: 8104827
Sorry...  that was everything up to the '@'.  This is correct.  Note the '-1' below:

SELECT CASE CHARINDEX('@', col_name)
         WHEN 0 THEN col_name
         ELSE LEFT(col_name, CHARINDEX('@', col_name) - 1)
      END AS col_name
FROM tbl_name
0
 

Author Comment

by:rrdiii2003
ID: 8105272
Perfect !  Works like a charm.
Many, Many Thanks malekam!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

765 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