Trimming Last 3 Numbers from some Records

Question

Trimming Last 3 Numbers from some Records

Guitar_player

SSCrazy

Points: 2991
More actions
September 1, 2014 at 1:12 am

#100567

Hi ,
I have column which are having data as Alphabets and also Numbers like to say
ID Column1
1 ABCD 123
2 XY 567
3 ghj 001
4 RUT
5 PUT
like that i have many records now i want to Trim the Last digits with numbers and keep only the string names like
ID Column1
1 ABCD
2 XY
3 ghj
4 RUT
5 PUT
Can you guys help me out please 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

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

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

Will the records always have a space in them and will it always be three numbers?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Guitar_player (9/1/2014)
Hi ,
I have column which are having data as Alphabets and also Numbers like to say
ID Column1
1 ABCD 123
2 XY 567
3 ghj 001
4 RUT
5 PUT
like that i have many records now i want to Trim the Last digits with numbers and keep only the string names like
ID Column1
1 ABCD
2 XY
3 ghj
4 RUT
5 PUT
Can you guys help me out please 🙂

REVERSE()

Then check the first four characters - if they are three digits followed by a space, use STUFF to remove the first four characters, otherwise leave alone

REVERSE()

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Here is another method, find the last character which is not space or numeric

😎

USE tempdb;

GO

;WITH SAMPLE_DATA(ID,Column1) AS

(SELECT ID,Column1 FROM

(VALUES

(1,'ABCD 123')

,(2,'XY 567')

,(3,'ghj 001')

,(4,'RUT')

,(5,'PUT')) AS X(ID,Column1)

)

,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

SELECT

SD.ID

,SUBSTRING(SD.Column1,1,MAX(NM.N)) AS CLEAN_COL

FROM SAMPLE_DATA SD

CROSS APPLY

(SELECT TOP(LEN(SD.Column1)) (LEN(SD.Column1) + 1) - ROW_NUMBER()

OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)

WHERE ASCII(SUBSTRING(SD.Column1,NM.N,1)) NOT IN (32,48,49,50,51,52,53,54,55,56,57)

GROUP BY SD.ID,SD.Column1;

Results

ID CLEAN_COL

----------- ---------

1 ABCD

2 XY

3 ghj

4 RUT

5 PUT

Guitar_player SSCrazy Points: 2991 More actions · Answer 4

Hi ALL,

Thanks for your Replies . I will try the Methods which are necessary and will let you know

Guitar_player SSCrazy Points: 2991 More actions · Answer 5

Eirikur Eiriksson (9/1/2014)
Here is another method, find the last character which is not space or numeric
😎

Hi Eiriksson,

There are also quite records like 'ABC DEF 210 ' , 'ggh / sah ggh 590'...

So does the logic applies for these too ?

If possible can i get any function directly for these ..to check if the last digits are numbers ..if they are numbers then delete them and also the sapce and if not Trim the last space

Thanks & Regards,

Guitar Player

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

;WITH SampleData AS (

SELECT MyString = 'ABC DEF 210 ' UNION ALL

SELECT 'ggh / sah ggh 590' UNION ALL

SELECT 'ggh / sah gghXPT' UNION ALL

SELECT 'ggh / sah ggh123')

SELECT *,

NewString = CASE

WHEN SUBSTRING(rMyString,4,1) = ' ' AND LEFT(rMyString,3) NOT LIKE '%[^0-9]%' THEN REVERSE(STUFF(rMyString,1,4,''))

ELSE MyString END

FROM SampleData

CROSS APPLY (SELECT rMyString = REVERSE(RTRIM(MyString))) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Guitar_player (9/1/2014)
Eirikur Eiriksson (9/1/2014)
Here is another method, find the last character which is not space or numeric
😎
Hi Eiriksson,
There are also quite records like 'ABC DEF 210 ' , 'ggh / sah ggh 590'...
So does the logic applies for these too ?
If possible can i get any function directly for these ..to check if the last digits are numbers ..if they are numbers then delete them and also the sapce and if not Trim the last space
Thanks & Regards,
Guitar Player

That should be no problem, the code trims the string to the last Alpha character in the string

This set

(1,'ABCD 123')

,(2,'XY 567')

,(3,'ghj 001')

,(4,'RUT')

,(5,'PUT')

,(6,'ABC DEF 210')

,(7,'ggh / sah ggh 590')

,(7,'ABC 123 DEF 456')

becomes this

ID CLEAN_COL

----------- -----------------

1 ABCD

2 XY

3 ghj

4 RUT

5 PUT

6 ABC DEF

7 ggh / sah ggh

7 ABC 123 DEF

sateeshcvtly Newbie Points: 9 More actions · Answer 8

Hi,

I think below one will be the easiest way to query your data

SELECT <Col_Name>,CASE WHEN PATINDEX('%[0-9]%',REVERSE(<Col_Name>))>0

THEN RTRIM(SUBSTRING(<Col_Name>,1,LEN(<Col_Name>)-(PATINDEX('%[0-9]%',REVERSE(<Col_Name>)))-2)) -- Please note, we have considered 3 numeric digits in this case

ELSE RTRIM(<Col_Name>) END AS New_String from <table>

Guitar_player SSCrazy Points: 2991 More actions · Answer 9

sateeshcvtly (9/2/2014)
Hi,
I think below one will be the easiest way to query your data
SELECT <Col_Name>,CASE WHEN PATINDEX('%[0-9]%',REVERSE(<Col_Name>))>0
THEN RTRIM(SUBSTRING(<Col_Name>,1,LEN(<Col_Name>)-(PATINDEX('%[0-9]%',REVERSE(<Col_Name>)))-2)) -- Please note, we have considered 3 numeric digits in this case
ELSE RTRIM(<Col_Name>) END AS New_String from <table>

Hey .. thanks yaar . That did the Trick 🙂