Strip Out the First part of a string

Question

Strip Out the First part of a string

Ranga846

Valued Member

Points: 64
More actions
November 11, 2009 at 6:50 am

#156395

IF OBJECT_ID('TEMPDB..#Test') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test
(
FName VARCHAR(20)
,ACC_Code VARCHAR(20)
)
INSERT #Test VALUES('RAY','RAY_M_MAHLANGU')
INSERT #Test VALUES('RAY','RAY_MAHLANGU')
INSERT #Test VALUES('RAY','RMAHLANGU')
INSERT #Test VALUES('ANT','ANT_1_KAPLAN')
INSERT #Test VALUES('ANT','AKAPLAN')
INSERT #Test VALUES('ANT','ANT_M_KAPLAN')
INSERT #Test VALUES('TONY','TONY')
INSERT #Test VALUES('TONY','TONY_1_2')
--TEST 1: Code That Extracts The first part of the ACC_Code before the underscore i.e RAY_M_MAHLANGU should strip out the RAY and ignore the rest
--- My code is tripping out the middle. How do I fix that
SELECT LTRIM(SUBSTRING(
ACC_Code,
CHARINDEX('_', ACC_Code) + 1,
CHARINDEX(
'_',
ACC_Code + '_', CHARINDEX('_', ACC_Code) + 1) -
CHARINDEX('_', ACC_Code) - 1)) AS NewCol,ACC_Code FROM #Test
Ranga

Viewing 4 posts - 1 through 3 (of 3 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I hope that I understood what you want, but I have to admit that I’m not so sure. I understood that you want to get only the part of the string that comes before the underscore. If the string has no underscore at all, then you want the whole string. The code bellow shows one way of doing so. If I misunderstood you, pleas explain your needs.

select left(ACC_Code, case when charindex('_',ACC_Code) = 0 then len(ACC_Code) else charindex('_',ACC_Code)-1 end) as NewCol, ACC_Code

from #Test

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ranga846 Valued Member Points: 64 More actions · Answer 2

Thanks....I was also trying other ways and also came up with this and it works too

SELECT ACC_Code = LTRIM(LTRIM(SUBSTRING(ACC_Code + '_', 0 + 1,CHARINDEX('_', ACC_Code + '_', 0 + 1) - 0 - 1 ))),ACC_Code FROM #Test

How would this be re-written to work with informix sql ?

Ranga

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

Solution provided by Adi cohn was very straightforward i thought!

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