Devora Devora - 6 months ago 8
SQL Question

selecting the correct letter from a string

CREATE TABLE #tmpTbl (m VARCHAR(100))
INSERT INTO #tmpTbl VALUES
(',[Undergraduate1]')
,(',[Undergraduate10]')
,(',[Undergraduate11]')
;
GO

select LEFT(m, PATINDEX('%[0-9]%', m)-1) as a,
SUBSTRING(m, PATINDEX('%[0-9]%', m), LEN(m)) as b from #tmpTbl


drop table #tmpTbl


Hi given the above tmptable and select statement, the result will be as follow.

a | b
-------------------------------------------------------
,[Undergraduate | 1]
,[Undergraduate | 10]
,[Undergraduate | 11]


However i want it to be like this.

a | b
-------------------------------------------------------
,[Undergraduate | 1
,[Undergraduate | 10
,[Undergraduate | 11


How can i achieve that? i tried alot of combination with PATINDEX, LEFT, RIGHT, SUBSTRING,LEN. but cant get right of the ] in column B

Answer

you can use replace to remove the ]. Dodgy, but it achieves what you want

select LEFT(m, PATINDEX('%[0-9]%', m)-1) as a,
     REPLACE(SUBSTRING(m, PATINDEX('%[0-9]%', m), LEN(m)),']','') as b from #tmpTbl

alternative: reverse the string, substring to remove 1st char, reverse back

select LEFT(m, PATINDEX('%[0-9]%', m)-1) as a,
         REVERSE(SUBSTRING(REVERSE(SUBSTRING(m, PATINDEX('%[0-9]%', m), LEN(m))),2,LEN(M))) as b from #tmpTbl