How to Delete Right 3 Characters from a string

Question

Post reply

How to Delete Right 3 Characters from a string

//Ravi

SSCrazy

Points: 2612
More actions
January 22, 2010 at 9:42 am

#140732

Hi,
I am trying to delete 3 characters from the right in a column. I can't find a function that will easily do that. The length of the string keeps changing. Below is some sample data. Please advise.
CA*461*NN
CA*461*NN
CA*461*NN
CA*463*NN
CA*463*NN
CCABM*100*01
CCART*100*01
CCART*100*01
CCICC*114*Z2
CCICC*Z2*Z2
CCLGA*110*01
CCLNG*100*01
CCLNG*100*01
CCLNG*102*01
CCSTD*103*01
CD*001*1S
CD*329*01
CD*329*31
CD*352*01
CD*352*01
CD*352*01
CD*352*01
CD*352*01
CD*352*01
CD*352*01
CD*352*01
CD*352*01
CS*762*51
CS*630*51
CS*772*61
SC*101D*01
SC*101D*02
SC*101*03
SC*101*04
SC*101D*05
------------
🙂

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Try this if this works..

UPDATE Table SET Column = REPLACE( Column, RIGHT(Column, 3), '' )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Does this help get you in the right direction?

declare @ TestStr varchar(32); -- to get this to post, I added a space between @ and TestStr, remove that space

set @TestStr = 'SC*101D*05';

select @TestStr = left(@TestStr, len(@TestStr) - 3);

select @TestStr;

//Ravi SSCrazy Points: 2612 More actions · Answer 3

Lynn,

Thanks. It worked. Here was the solution.

SELECT left (column_name, len(column_name) -3) FROM Table_Name

------------
🙂

LutzM SSC Guru Points: 107049 More actions · Answer 4

DECLARE @v-2 VARCHAR(30)

SET @v-2='CA*461*NN'

SELECT LEFT(@v,LEN(@v)-3)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sugsy SSC Enthusiast Points: 100 More actions · Answer 5

Just for the record Kingston's suggestion works fine n'all

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Kingston Dhasian (1/22/2010)
Try this if this works..
UPDATE Table SET Column = REPLACE( Column, RIGHT(Column, 3), '' )

This won't work if the last 3 characters are used in the same sequence elsewhere in the string. I'd really go with the left(len)) option here.

drew.allen SSC Guru Points: 76985 More actions · Answer 7

Sugsy (7/22/2011)
Just for the record Kingston's suggestion works fine n'all

Actually, it doesn't. It works as long as the last three characters aren't repeated anywhere else in the string, but will not work if the characters are repeated. For example, if the original string is "CH*012*01", Kingston's solution will produce "CH2" whereas the correct solution is "CH*012'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Sugsy (7/22/2011)
Just for the record Kingston's suggestion works fine n'all

Just for the record, it will also delete the wrong data if the right 3 occurs more than once in the string. Don't use it. 😉

(Sorry, Kingston)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Gah... I've got to learn to read the rest of the posts first. Tow other folks basically said the same thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Jeff Moden (7/22/2011)
Gah... I've got to learn to read the rest of the posts first. Tow other folks basically said the same thing.

I would point out the obvious that there's a reason you're #1 non employee-poster. But that's been said already :hehe:.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Ninja's_RGR'us (7/22/2011)
Jeff Moden (7/22/2011)
Gah... I've got to learn to read the rest of the posts first. Tow other folks basically said the same thing.
I would point out the obvious that there's a reason you're #1 non employee-poster. But that's been said already :hehe:.

BWHAA-HAAA!!!! And the pot calls the kettle black. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Jeff Moden (7/23/2011)
Ninja's_RGR'us (7/22/2011)
Jeff Moden (7/22/2011)
Gah... I've got to learn to read the rest of the posts first. Tow other folks basically said the same thing.
I would point out the obvious that there's a reason you're #1 non employee-poster. But that's been said already :hehe:.
BWHAA-HAAA!!!! And the pot calls the kettle black. 😉

Here you go, 3 free points. Tx for closing my case :-D.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Ya know, Remi... I thought you were just joking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Jeff Moden (7/23/2011)
Ya know, Remi... I thought you were just joking.

Of course I am. Why would you think otherwise ;-).