SQL Script patindex

Question

I think i have some syntax error in my script but can't figure out where.

I want to select the Integer that falls between a pair of ( ) begining from the right of a cell? Reason being, there might be another pair of brackets containing characters

and what if some records are w/o close brackets for some reason..

e.g.

Period | ProgrammeName             |  
Jan    | ABC (Children) (30)       |  
Feb    | Helloworld (20T (20)      |  

result: 30 20

select
    Period,
    ProgrammeName,
     substring(ProgrammeName,(len(ProgrammeName) - (patindex('%(%', Reverse(ProgrammeName)))+2),(len(ProgrammeName)-1))
 from
    Table

but it only displays
30)
20)

i have been manipulating it so that it doesn't extract ')', but can't get the expected results.

Answer 1

So, you need to grab whatever's between the final set of open and closing brackets at the end of a string, right?

First off, find the first opening bracket from the end of the string. I'd use CHARINDEX, as you're just looking for a single character; you don't need to use pattern matching.

SELECT LEN(ProgrammeName) + 1 - CHARINDEX('(', REVERSE(ProgrammeName)) FROM Table

Then, find the first closing bracket from the end of the string:

SELECT LEN(ProgrammeName) + 1 - CHARINDEX(')', REVERSE(ProgrammeName)) FROM Table

Then, put those together. To use SUBSTRING, you need the position of the first character, then the length of the string you want, so you need the first result (the position of the '('), and then the second result minus the first result, to get the length of the bracketed bit, as a starting point:

SELECT (LEN(ProgrammeName) + 1 - CHARINDEX(')', REVERSE(ProgrammeName))) - (LEN(ProgrammeName) + 1 - CHARINDEX('(', REVERSE(ProgrammeName))) FROM Table

You also need to do a bit of fiddling to extract the part between the brackets, leaving the brackets alone. That's explained in the comments in this final example, where the final expression should be doing the job you want:

SELECT
        -- Position of first bracket
        LEN(ProgrammeName) + 1 - CHARINDEX('(', REVERSE(ProgrammeName)),
        -- Position of second bracket
        LEN(ProgrammeName) + 1 - CHARINDEX(')', REVERSE(ProgrammeName)),
        -- Position of second bracket minus position of first bracket gives length
        (LEN(ProgrammeName) + 1 - CHARINDEX(')', REVERSE(ProgrammeName))) - (LEN(ProgrammeName) + 1 - CHARINDEX('(', REVERSE(ProgrammeName))),
        -- If we want to extract the bit between the brackets, we need to start from the bracket position
        -- plus one character, and knock one off the length, to avoid grabbing the closing bracket.
        SUBSTRING(ProgrammeName, 1 + LEN(ProgrammeName) + 1 - CHARINDEX('(', REVERSE(ProgrammeName)), (LEN(ProgrammeName) + 1 - CHARINDEX(')', REVERSE(ProgrammeName))) - (LEN(ProgrammeName) + 1 - CHARINDEX('(', REVERSE(ProgrammeName))) - 1)
FROM 
        Table

I've broken my answer down so you can see how I approach problems like these -- do them one bit at a time, checking the results as you go along, and it's easier to get your head around.