Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Regular Expression in SELECT Statement

    Hi Guys,
    Can anyone suggest me on the below mentioned issue?

    I have a table named Table_1 where in the column name Col_1 has values as per mentioned below...

    New Account - ABC
    New Account - JFJ
    New Account - FGD
    New Account - TYY
    New Account - DFG
    New Account - FGD- This is my house
    New Account - JFJ- How are you
    New Account - ASD- Hello World
    .................................................. ....
    .................................................. ....

    I need to run a select statement on the table_1 where in I want to filter the records based on the col_1 field with the below logic...

    The resultset should contain only those rows which has the col_1 values LIKE any one of these...... '%ABC%', '%JFJ%', '%FGD%', '%TYY%', '%DFG%'

    I tried to right the regex for this, but no fruitfull result. Do help me in getting this....


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DBA_Rahul
    The resultset should contain only those rows which has the col_1 values LIKE any one of these...... '%ABC%', '%JFJ%', '%FGD%', '%TYY%', '%DFG%'
    You've pretty well written it already:
    Code:
    WHERE col_1 values LIKE '%ABC%' OR col_1 values LIKE '%JFJ%' OR col_1 values LIKE '%FGD%'.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Another option
    Code:
    SELECT a.col_1
    FROM   table_1 As a
     INNER
      JOIN (
            SELECT 'ABC' As match
            UNION ALL SELECT 'JFJ'
            UNION ALL SELECT 'FGD'
            UNION ALL SELECT 'TYY'
            UNION ALL SELECT 'DFG'
           ) As b
        ON CharIndex(b.match, a.col_1) > 0
    *shrug*
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nice George - I like it. 2 words of caution:
    LIKE will be faster than CHARINDEX (try a benchmark).
    DISTINCT might be needed if a value could contain two or more values - maybe EXISTS would be superior.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •