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


  • 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 "



    • 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


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


    SELECT *
    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:
    MCCA 2011
    Monday, May 23, 2011 10:41 AM