blablablabla(XYZ)

DECLARE @bla VARCHAR(50)
SET @bla = 'blablablabla(XYZ)asdsdsad'

SELECT SUBSTRING(
         @bla,
         CHARINDEX('(', @bla) + 1,
         CHARINDEX(')', @bla) - CHARINDEX('(', @bla) - 1
       )

'XYZ'

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

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

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')

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

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

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 YourTable
    SET YourColumn=SUBSTRING(YourColumn,CHARINDEX('(',YourColumn)+1,CHARINDEX(')',YourColumn)-(CHARINDEX('(',YourColumn))-1)
    WHERE xxx=yyy