November 11, 2009 at 6:50 am
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
November 11, 2009 at 7:00 am
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/
November 11, 2009 at 9:34 am
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
November 11, 2009 at 11:53 am
Solution provided by Adi cohn was very straightforward i thought!
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply