?
Solved

SQL Statement To Trim Characters

Posted on 2003-03-07
28
Medium Priority
?
653 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
  • 11
  • 6
  • 4
  • +3
28 Comments
 
LVL 70

Expert Comment

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

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 70

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 70

Expert Comment

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…
Suggested Courses

621 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