## Trimming Last 3 Numbers from some Records

 Author Message Guitar_player SSCommitted Group: General Forum Members Points: 1699 Visits: 236 Hi , I have column which are having data as Alphabets and also Numbers like to say ID Column11 ABCD 1232 XY 5673 ghj 001 4 RUT5 PUTlike that i have many records now i want to Trim the Last digits with numbers and keep only the string names like ID Column11 ABCD2 XY3 ghj 4 RUT5 PUTCan you guys help me out please :-) Neil Burton SSChampion Group: General Forum Members Points: 12136 Visits: 11431 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 ChrisM@Work SSC Guru Group: General Forum Members Points: 96822 Visits: 20698 Guitar_player (9/1/2014)Hi , I have column which are having data as Alphabets and also Numbers like to say ID Column11 ABCD 1232 XY 5673 ghj 001 4 RUT5 PUTlike that i have many records now i want to Trim the Last digits with numbers and keep only the string names like ID Column11 ABCD2 XY3 ghj 4 RUT5 PUTCan 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 aloneREVERSE() “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps Eirikur Eiriksson SSC Guru Group: General Forum Members Points: 93885 Visits: 20677 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_COLFROM SAMPLE_DATA SDCROSS 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 ABCD2 XY3 ghj4 RUT5 PUT` Guitar_player SSCommitted Group: General Forum Members Points: 1699 Visits: 236 Hi ALL,Thanks for your Replies . I will try the Methods which are necessary and will let you know Guitar_player SSCommitted Group: General Forum Members Points: 1699 Visits: 236 Eirikur Eiriksson (9/1/2014)Here is another method, find the last character which is not space or numericHi 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 Group: General Forum Members Points: 96822 Visits: 20698 ` ;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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps Eirikur Eiriksson SSC Guru Group: General Forum Members Points: 93885 Visits: 20677 Guitar_player (9/1/2014)Eirikur Eiriksson (9/1/2014)Here is another method, find the last character which is not space or numericHi 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 PlayerThat should be no problem, the code trims the string to the last Alpha character in the stringThis 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 ABCD2 XY3 ghj4 RUT5 PUT6 ABC DEF7 ggh / sah ggh7 ABC 123 DEF` sateeshcvtly Forum Newbie Group: General Forum Members Points: 5 Visits: 3 Hi,I think below one will be the easiest way to query your dataSELECT ,CASE WHEN PATINDEX('%[0-9]%',REVERSE()>0 THEN RTRIM(SUBSTRING(,1,LEN(-(PATINDEX('%[0-9]%',REVERSE())-2)) -- Please note, we have considered 3 numeric digits in this caseELSE RTRIM( END AS New_String from Guitar_player SSCommitted Group: General Forum Members Points: 1699 Visits: 236 sateeshcvtly (9/2/2014)Hi,I think below one will be the easiest way to query your dataSELECT ,CASE WHEN PATINDEX('%[0-9]%',REVERSE()>0 THEN RTRIM(SUBSTRING(,1,LEN(-(PATINDEX('%[0-9]%',REVERSE())-2)) -- Please note, we have considered 3 numeric digits in this caseELSE RTRIM( END AS New_String from Hey .. thanks yaar . That did the Trick :-)