CODEWITHSUNDEEP
sqlsql-server
Gallop
Gallop

Reputation: 1405

Select column data between () parantheses

I have data in SQL server database column like the following:

Column

Ot ntri - Non Cash - (6932)
Otr Contri- Cash - (6930)
anth C-Cash - (6935)
Phil Cor-Non Cash - (6937)
Poll Conh - (6940)

I need a query to select data that is present only withing the parantheses ().

Please help:

I need to select only

6932
6930
6935

etc for the column

Thanks

Upvotes: 1

Views: 90

Answers (2)

Kaf
Kaf

Reputation: 33809

I know you have accepted a good answer. This is another way of doing it. Same functions are used with CTE:

SQL server 2008 fiddle example to get the string within last brackets. 

;with cte as (
 select col, charindex(')',reverse(col),1) brk1,
        charindex('(',reverse(col),1) brk2
 from t
)
select col, reverse(substring(reverse(col),brk1+1,brk2-brk1-1)) mystr
from cte

Upvotes: 2

JanW
JanW

Reputation: 1849

A combination of SUBSTRING and CHARINDEX could do this for you:

SELECT  * ,
    REVERSE(t.[Column]) AS Reversed ,
    CASE WHEN t.[Column] LIKE '%(%'
              AND t.[Column] LIKE '%)%'
         THEN REVERSE(SUBSTRING(REVERSE(t.[Column]),
                                CHARINDEX(')', REVERSE(t.[Column])) + 1,
                                CHARINDEX('(', REVERSE(t.[Column]))
                                - CHARINDEX(')', REVERSE(t.[Column])) - 1))
         ELSE NULL
    END AS result
FROM    dbo.[Table] AS t

Upvotes: 2

Related Questions