selecting the correct letter from a string

Question

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

`SUBSTRING(m, PATINDEX('%[0-9]%', m), LEN(m) - 1)` ? – Florian Genser May 30 '16 at 07:43 — Florian Genser, May 30 '16 at 07:43

Nick Kavadias · Accepted Answer · 2016-05-30T01:52:16.593

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

score 3 · Answer 2 · answered May 30 '16 at 01:45

3

You can use REPLACE to replace ] with ''

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

answered May 30 '16 at 01:45

sqluser

5,502
7
36
50

score 3 · Answer 3 · answered May 30 '16 at 01:47

3

I'm inclined to use stuff() for this purpose:

select replace(stuff(m, 1, patindex(m, '%[0-9]%'), ''), ']', '')

answered May 30 '16 at 01:47

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 4 · answered May 30 '16 at 01:55

Here is an alternative approach that will strip any text and just leave the numbers behind.

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(m, pos, LEN(m))
    FROM (
        SELECT m, pos = PATINDEX('%[0-9]%', m)
        FROM #tmpTbl
    ) d
) t

selecting the correct letter from a string

4 Answers4