SQL Update - Everything inside ()

Question

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.

Answer 1

In MySQL:

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

Answer 2

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

Answer 3

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

Answer 4

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