none
WHERE CONTAINS() / INSTR() / ... more variable parameters at a time

    Question

  • In a Select... From... Where... sqlsentence within a vbamodule, I want to surch in a Databasefield all the records that start with one of the given strings with variable lenght. The list of strings is also variable (1, 2, 3 or more)

    f.i. all the records that start with (121% or 87% or 4221%)    or another ex. all the records that start with 1(37% or 93% or 8% or 3302%)

    I tried with:

    WHERE CONTAINS(Databasefield, ... ) -> offers the possibility to define more variable strings at a time but it doesn't work (problems with double quotes or sql-version?)

    WHERE Databasefield IN( ) -> in combination with LEFT() but then all the strings need to have the same lenght;

    WHERE Databasefield LIKE ... -> but then I have to repeat 'LIKE' manually with each string

    WHERE INSTR(Databasefield, ... ) -> does work but only for one string at a time

    It would be great if I could get all the data at a time with a query like this:

    SELECT ... FROM ... WHERE INSTR(Databasefield,  '121' or '87' or '422') = 1 "

    thanks

    mvmb

    • Moved by Tom PhillipsModerator Monday, May 23, 2011 1:52 PM TSQL question (From:SQL Server Database Engine)
    Monday, May 23, 2011 10:17 AM

Answers

  • I think only the following construct will work for you:

     

    SELECT *
    FROM TABLE_NAME
    WHERE DB_COL like '121%'
    OR DB_COL like '87%'
    OR DB_COL like '422%'
    ...
    OR DB_COL like 'xyz%'
    
    

    Try creating dynamic SQL buy generating the WHERE clause dynamically and storing in a VARCHAR variable, declare @sql varchar(max). You can use simple WHILE Loop or CURSOR to create one.

    Finally execute the dyn-SQL with EXEC(@sql) statement.

    You can also put the whole logic in a proc and execute the proc from front-end.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Monday, May 23, 2011 10:41 AM