0

I have a string value 'Marquez, Gabriel Garcia. (A123456)'. I need to extract the values that appear in '(' and ')'. In order words, I need the result to say display 'A123456'

I have tried using a right function, but that did not create the desired result. 

select right('Marquez, Gabriel Garcia. (A123456)' ,  8)
Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
user716255
  • 352
  • 1
  • 6
  • 18

2 Answers2

2

String manipulation is tricky in SQL Server. I find that cross apply simplifies the logic:

select left(v2.str, charindex(')', v2.str) - 1) as stuff_between_parens
from (values ('Marquez, Gabriel Garcia. (A123456)')) v(str) cross apply
     (values (stuff(str, 1, charindex('(', str), ''))) v2(str)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2
DECLARE @str VARCHAR(100) = 'Marquez, Gabriel Garcia. (A123456)'

SELECT  SUBSTRING(@str,CHARINDEX('(',@str) +1 ,CHARINDEX(')',@str) - CHARINDEX('(',@str) - 1)
Hasan Mahmood
  • 978
  • 7
  • 10
  • 2
    Thanks Hasan. That did the trick. Could explain the logic of substring/charindex? I have not been able to find a good explanation. – user716255 Mar 22 '19 at 20:35
  • @user716255: try this for substring : `SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;` : its returning a sub string with start index and length. CHARINDEX return the character index from a string. hope it help. if it works for you can you please mark as answer? as well as mask as this answer is useful. – Hasan Mahmood Mar 22 '19 at 20:45
  • The documentation for SUBSTRING and CHARINDEX are pretty detailed and clear... just google "SQL Server SUBSTRING" and look at the Microsoft SQL Server documentation. This is very basic string parsing, on par with most languages. – pmbAustin Mar 22 '19 at 22:02