Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Like clause in SQL Query

    Hi folks,

    How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause:
    select * from course where (name like 'sci%' or name like 'eng%')

    Can I use regular expressions to achieve that?

    Thanks!
    -Parul

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    SQL Server does not have support for RegExp. You could create your own .NET Function for this, however this would perform suboptimal compared to the code you already have, as your code can use indexes efficiently. Thus, my advice is to leave the code as it is.

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    Can we do something on the lines of the following:
    select * from course
    where [name] like '['sci','eng']%'

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with OR? are you having a performance problem?

    try this --

    select * from course
    where [name] like 'sci%'
    union all
    select * from course
    where [name] like 'eng%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2002
    Posts
    74
    it's part of an application that allows only a single WHERE clause and the column name cannot be specified twice.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i see

    i guess it doesn't help if i say that the guy who designed the app should be shot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2007
    Posts
    62
    select * from course
    inner join
    (
    select 'sci' c1 union all select 'eng'
    ) x
    on name like c1+'%'

    even better if you already have the table of partial names.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ooh! Ooh! Can I try?
    Code:
    --Finds all strings containing 'sci' or 'eng'
    select	*
    from	course
    where	len(replace(replace(name, 'sci', ''), 'eng', '')) < len(name)
    
    --Finds all strings starting with 'sci' or 'eng'
    select	*
    from	course
    where	len(replace(replace(left(name, 3), 'sci', ''), 'eng', '')) < 3
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Oct 2002
    Posts
    74
    hi blindman,
    the second query is giving me other names as well...

  10. #10
    Join Date
    Oct 2002
    Posts
    74
    can we specify strings instead of characters in regular expressions?

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ParulV
    it's part of an application that allows only a single WHERE clause and the column name cannot be specified twice.

    Ah 4GL...never worth their salt

    Is it calling a sproc? In any case it's dynamic and can probably be hacked
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Oct 2002
    Posts
    74
    Its not calling any stored proc. I am just wondering if I can use regular expressions and match strings instead of single characters. Do reg exp allow for that?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ooh! ooh! can i try?

    where charindex('sci',name)+charindex('eng',name) = 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ParulV
    hi blindman,
    the second query is giving me other names as well...
    Hmm...I suppose it would eroneously return names less than three characters long. Is that what is happening?

    Try this:
    Code:
    --Finds all strings starting with 'sci' or 'eng'
    select	*
    from	course
    where	replace(left(name, 3), 'sci', 'eng') = 'eng'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here's my shot

    Code:
    SET NOCOUNT ON
    CREATE TABLE myTable99([name] varchar(100))
    GO
    
    INSERT INTO myTable99([name])
    SELECT 'Science of mixology' UNION ALL
    SELECT 'Engineering of mixology' UNION ALL
    SELECT 'Bachelor of Go Go' UNION ALL
    SELECT 'Dr. Drunk'
    GO
    
    DECLARE @like table([name] varchar(100))
    INSERT INTO @like([name]) SELECT 'sci' UNION ALL SELECT 'eng'
    
    SELECT * FROM myTable99 t JOIN @like l ON t.[name] LIKE l.[name]+'%'
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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