SQL Update - Everything inside ()

Hey guys.

I have a column of data containing a string with the following format:

blablablabla(XYZ)

and I would like to discard everything outside the () - and the parenthesis themselves - and to set update that field with the value inside the (). This implies that, in this particular case, the "blablabla" would be discarded and that entry would only contain XYZ.

I know that I have to use SQL UPDATE, but i'm not sure on how to build the where condition properly.

Many thanks, Hal

EDIT: i forgot to say that it was in SQL Server 2008. Thank you all for your answers, they all worked (i tested). Too bad i can't mark all as correct. I'm really amazed, the answers were all so quick.

From stackoverflow

• In MySQL:

  UPDATE  mytable
  SET     column = SUBSTRING(column, INSTR(column, '(') + 1, INSTR(column, ')') - INSTR(column, '(') - 1)
  WHERE   column RLIKE ('\\(.*\\)')
  

  Dave Costa : Might want to add WHERE column LIKE '%(%)%', so you don't try to update rows that don't match the pattern

  Hal : I forgot to say that it was in SQL Server 2008. Thank you for you solution, i gave +1

  Quassnoi : @Dave Costa: right.

• This assumes that there is exactly one pair of correctly nested parentheses (and is T-SQL syntax):

  DECLARE @bla VARCHAR(50)
  SET @bla = 'blablablabla(XYZ)asdsdsad'
  
  SELECT SUBSTRING(
           @bla, 
           CHARINDEX('(', @bla) + 1, 
           CHARINDEX(')', @bla) - CHARINDEX('(', @bla) - 1
         )
  

  Yields:

  'XYZ'
  

  ---

  EDIT: This checks for various ill-formatted strings and could be used in a WHERE clause (e.g. WHERE ... <> 'no match'):

  SELECT
    CASE 
      WHEN 
        /* check for '(' and ')' */
        CHARINDEX('(', @bla) > 0 AND CHARINDEX(')', @bla) > CHARINDEX('(', @bla)
        /* check for 'bla(bla(XYZ)bla' */
        AND CHARINDEX('(', @bla, CHARINDEX('(', @bla) + 1) = 0
        /* check for 'bla(XYZ)bla)bla' */
        AND CHARINDEX(')', @bla, CHARINDEX(')', @bla) + 1) = 0
      THEN SUBSTRING(@bla, 
            CHARINDEX('(', @bla) + 1, 
            CHARINDEX(')', @bla) - CHARINDEX('(', @bla) - 1
          ) 
      ELSE 'no match'
    END
  

  Learning : +1 for condition outlining the nested parenthesis part.

  Learning : Actually , won't this give error if the data does not have parenthesis?

  Tomalak : That's why I included the "assumes exactly one pair" part. :-)

  Hal : Works great, thanks :) +1

• MSSQL Solution. The function you're looking for is CharIndex Simple table called test containing one column called [name]

  Code To Insert Entries

  INSERT INTO TEST (name) VALUES ('SomeString(test1)')
  INSERT INTO TEST (name) VALUES ('test2')
  INSERT INTO TEST (name) VALUES ('SomeOtherString(test3)')
  INSERT INTO TEST (name) VALUES ('test4')
  

  SQL Code to Find The Relevant Entries

  SELECT *, 
      charindex('(', name), 
      charindex(')', name),
      substring(
       name,
       charindex('(', name) + 1,
       charindex(')', name) - charindex('(', name) - 1
      )
  FROM 
      TEST 
  WHERE 
      name like '%(%)%'
  

  SQL Code to update entries

  UPDATE 
      TEST
  SET
      name = substring(
       name,
       charindex('(', name) + 1,
       charindex(')', name) - charindex('(', name) - 1
      )
  WHERE
      name like '%(%)%'
  

  Tomalak : Heh, I managed to copy your solution before you even posted it. :-P

  Eoin Campbell : heh! that's what I get for actually writing in Mngt Studio instead of off the top of my head :)

  Tomalak : Well, actually I wrote up mine in SSMS as well, not getting the string index calculations right with sub-string functions is just too embarrassing. ;-)

  Learning : +1 for the correct "where" condition.

  Tomalak : @Learning: I'd say it's more of an approximation. It would also match invalid stuff like "blabla)(bla(XYZ)bla". If this can happen depends on the OP's situation, of course.

  Hal : Works fine thanks!

• for sql server

  declare @x  varchar(100)
  
  set @X= 'fgjfkfgkjz(12345)'
  set @X= '()'
  set @X= '(1234)'
  set @X= 'fgjfkfgkjz()dfddf'
  set @X= 'fgjfkfgkjz(123)dfddf'
  
  PRINT '>>'+SUBSTRING(@x,CHARINDEX('(',@x)+1,CHARINDEX(')',@x)-(CHARINDEX('(',@x))-1)+'<<'
  

  update command:

  UPDATE YourTable
      SET YourColumn=SUBSTRING(YourColumn,CHARINDEX('(',YourColumn)+1,CHARINDEX(')',YourColumn)-(CHARINDEX('(',YourColumn))-1)
      WHERE xxx=yyy