2

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

Ryan Abarquez
  • 307
  • 2
  • 6
  • 16
  • 1
    SQL can do this, but you should think about changing your database design. What have you tried so far? Look into CharIndex and Substring for starters. – Sparky Feb 10 '16 at 06:37
  • SUBSTRING and PATINDEX/CHARINDEX, or better to handle display issues in a more appropriate code layer. – Allan S. Hansen Feb 10 '16 at 06:37

3 Answers3

8

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
wiretext
  • 3,302
  • 14
  • 19
0

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
Shiju Shaji
  • 1,682
  • 17
  • 24
0

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
Thom A
  • 88,727
  • 11
  • 45
  • 75
Pradeep
  • 11
  • 3