none
SELECT SUBSTRING

    Question

  • Hi Guys,

     

    I have data as follow:

     

    CREATE TABLE #Table1 ([Name] varchar(60))

    INSERT INTO #Table1 ([Name]) Values ('IRENE RIVERA        (MOTHER)            ')

    INSERT INTO #Table1 ([Name]) Values ('KATHY BARHIGHT       (SISTER)          ')

    INSERT INTO #Table1 ([Name]) Values ('FLORES,EDUARDO      (BROTHER      ')

    INSERT INTO #Table1 ([Name]) Values ('HERNANDEZ,FRANCISCO P (FRIEND  ')

    INSERT INTO #Table1 ([Name]) Values ('LORETTA BUSTOS SISTER                 ')

     

    I'd like to select only the characters after '(', how do I do that?  I am not familiar with the use of SUBSTRING.  Thanks.

     

    Friday, July 20, 2007 11:53 PM

Answers

  • Try:

     

    Code Snippet

    select

        rtrim(replace(parsename(replace([Name], '(', '.'), 1), ')', '')) as c1

    from

        #Table1

    where

        [Name] like '%(%'

    go

     

     

     

    AMB

    Saturday, July 21, 2007 12:10 AM

All replies

  • Try:

     

    Code Snippet

    select

        rtrim(replace(parsename(replace([Name], '(', '.'), 1), ')', '')) as c1

    from

        #Table1

    where

        [Name] like '%(%'

    go

     

     

     

    AMB

    Saturday, July 21, 2007 12:10 AM
  • Very nice Alejandro!
    Saturday, July 21, 2007 12:25 AM
  • Thanks,

     

    It works well, but i do not really know much about replace and parsing.  I'd also like to know how to select that characters outside ().  but i will try to work on it myself, and see if i can get it right. Or else, i might need your help again, Thanks!

    Saturday, July 21, 2007 12:33 AM
  • CREATE TABLE #Table1 ([Name] varchar(60))

    INSERT INTO #Table1 ([Name]) Values ('IRENE RIVERA        (MOTHER)            ')

    INSERT INTO #Table1 ([Name]) Values ('KATHY BARHIGHT       (SISTER)          ')

    INSERT INTO #Table1 ([Name]) Values ('FLORES,EDUARDO      (BROTHER      ')

    INSERT INTO #Table1 ([Name]) Values ('HERNANDEZ,FRANCISCO P (FRIEND  ')

    INSERT INTO #Table1 ([Name]) Values ('LORETTA BUSTOS SISTER                 ')

     

    SELECT DISTINCT CASE WHEN [Name] LIKE '%(%' THEN

    rtrim(replace(parsename(replace([Name], '(', '.'), 2), ')', ''))

    ELSE [Name] END AS c1,

    CASE WHEN [Name] LIKE '%(%' THEN

    rtrim(replace(parsename(replace([Name] , '(', '.'), 1), ')', ''))

    END as c2

    FROM #Table1

     

    DROP TABLE #Table1

     

     

    Thanks y'all.

    Sunday, July 22, 2007 2:36 AM