SQL Select values inside parenthesis

问题:

I have a column in my table that has the following values

|         loan_no                  |

|  SSS Pensioner Loan (C000000001) |
|  SSS SSS Loan (C000000002)       |
|  SSS Salary Loan (C000000007)    |
|  SSS PAGIBIG Loan (C000000003)   |

How can I select only the values inside the parenthesis to have an outcome like this:

|  loan_no    |

|  C000000001 |
|  C000000002 |
|  C000000007 |
|  C000000003 |

Thank You in advance :) I'm using SQL Server 2008 R2

回答1:

CHARINDEX will help you Reference

declare @temp table        
(val nvarchar(77))

insert into @temp values ('SSS Pensioner Loan (C000000001)')
insert into @temp values ('SSS SSS Loan (C000000002)      ')
insert into @temp values ('SSS Salary Loan (C000000007)   ')
insert into @temp values ('SSS PAGIBIG Loan (C000000003)  ')



SELECT 
SUBSTRING(val,CHARINDEX('(', val) + 1,CHARINDEX(')', val) - CHARINDEX('(', val) - 1) as Val
from @temp  

回答2:

Try This..

declare @table table (loan nvarchar(100))
insert into @table
select 'SSS Pensioner Loan (C000000001)'
union all select 'SSS SSS Loan (C000000002)'   
union all  select 'SSS Salary Loan (C000000007)'   
  union all select 'SSS PAGIBIG Loan (C000000003)'


  select substring (loan,CHARINDEX('(', loan)+1,CHARINDEX(')', loan)-CHARINDEX('(', loan)-1)
  from @table

回答3:

You can use substring and instr function for mysql (for sql server you can use charindex in place of instr) follows:

SELECT SUBSTRING(loan_no, INSTR(loan_no, '(')+1,INSTR(loan_no, ')')-INSTR(loan_no, '(')-1) AS loan_no, INSTR(loan_no, ')') AS loan_no

FROM Table