Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to Delete Right 3 Characters from a string Expand / Collapse
Author
Message
Posted Friday, January 22, 2010 9:42 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:03 AM
Points: 97, Visits: 362
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


------------
:)
Post #852150
Posted Friday, January 22, 2010 10:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 2,661, Visits: 4,734
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/
Post #852160
Posted Friday, January 22, 2010 10:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 20,704, Visits: 32,351
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;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #852162
Posted Friday, January 22, 2010 10:17 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:03 AM
Points: 97, Visits: 362
Lynn,

Thanks. It worked. Here was the solution.

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


------------
:)
Post #852174
Posted Friday, January 22, 2010 10:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 6,829, Visits: 13,290
DECLARE @v VARCHAR(30)
SET @v='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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #852189
Posted Friday, July 22, 2011 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:03 AM
Points: 3, Visits: 70
Just for the record Kingston's suggestion works fine n'all
Post #1146775
Posted Friday, July 22, 2011 10:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
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.
Post #1146782
Posted Friday, July 22, 2011 11:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1146824
Posted Friday, July 22, 2011 4:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1146969
Posted Friday, July 22, 2011 4:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1146971
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse