SQL Server using wildcard within IN

Question

Since I believe this should be a basic question I know this question has probably been asked  but I am unable to find it  I m probably about to earn my Peer Pressure badge  but I ll ask anyway   Is there a way in SQL Server that I am not aware of for using the wildcard character   when using IN   I realize that I can use OR s like   select   from jobdetails where job no like  0711   or job no like  0712     and in some cases I can use a subquery like   select   from jobdetails where job no in  select job no from jobs where job id   39    but I m looking to do something like the following   select   from jobdetails where job no in   0711     0712      In this case it uses the percent sign as a character instead of a wildcard character so no rows are returned  I currently just use a bunch of OR s when I have to do this  but I know there has to be a better way  What method do you use for this

User · Answer

Try this  select    from jobdetails  where job no between  0711  and  0713    the only problem is that job  0713  is going to be returned as well so can use  07299999999999  or just add and   job no  lt  gt   0713   Dan zamir

User · Answer

As Jeremy Smith posted it  i ll recap  since I couldn t answer to that particular question of his   select   from jobdetails where job no like  071 1-2      If you just need 0711  and 0712  you can also place a ranges within the brackets  For the NOT keyword you could also use   1-2

User · Answer

I firstly added one off static table with ALL possibilities of my wildcard results  this company has a 4 character nvarchar code as their localities and they wildcard their locals  i e  they may have 456  which would give them 456 1  to 456 Z  i e 0-9  amp  a-z I had to write a script to pull the current user  declare them  and pull the masks for the declared user  Create some temporary tables just basic ones to rank the row numbers for this current user loop through each result  YOUR Or this Or that etc     Insert into the test Table    Here is the script I used   Drop Table  UserMasks  Drop Table  TESTUserMasks   Create Table  TESTUserMasks        User   Int  NOT NULL       Mask   Nvarchar  10  NOT NULL   Create Table  UserMasks        RN   Int  NOT NULL       Mask   Nvarchar  10  NOT NULL   DECLARE  User INT SET  User   74054  Insert Into  UserMasks  select ROW NUMBER   OVER   PARTITION BY ProntoUserID ORDER BY Id DESC  AS RN         REPLACE mask         Mask from dbo Access Masks  where prontouserid    User  DECLARE  TopFlag INT SET  TopFlag   1  WHILE   TopFlag  lt   select COUNT    from  UserMasks   BEGIN     Insert Into  TestUserMasks      select   User  Code from dbo MaskArrayLookupTable      where code like  select Mask       from  UserMasks Where RN    TopFlag       SET  TopFlag    TopFlag   1 END GO  select   from  TESTUserMasks

User · Answer

In Access SQL  I would use this   I d imagine that SQLserver has the same syntax   select   from jobdetails where job no like  0711   or job no like  0712

User · Answer

SELECT c   FROM  SELECT  071235  AS token UNION ALL SELECT  07113    UNION ALL SELECT  071343  UNION ALL SELECT  0713SA  UNION ALL SELECT  071443   AS c JOIN   SELECT  0712   AS pattern UNION ALL SELECT  0711     UNION ALL SELECT  071343   AS d ON c token LIKE d pattern  071235 07113 071343

User · Answer

You have the answer right there in your question  You cannot directly pass wildcard when using IN  However  you can use a sub-query    Try this   select   from jobdetails where job no in   select job no from jobdetails where job no like  0711   or job no like  0712        I know that this looks crazy  as you can just stick to using OR in your WHERE clause  why the subquery  How ever  the subquery approach will be useful when you have to match details from a different source   Raj

User · Answer

This might me the most simple solution use like any  select   from jobdetails where job no like any   0711     0712      In Teradata this works fine

User · Answer

The IN operator is nothing but a fancy OR of     comparisons  In fact it is so  nothing but  that in SQL 2000 there was a stack overflow bug due to expansion of the IN into ORs when the list contained about 10k entries  yes  there are people writing 10k IN entries      So you can t use any wildcard matching in it

User · Answer

I think I have a solution to what the originator of this inquiry wanted in simple form  It works for me and actually it is the reason I came on here to begin with  I believe just using parentheses around the column like   text   in combination with ORs will do it   select   from tableName where  sameColumnName like   findThis   or sameColumnName like   andThis   or  sameColumnName like   thisToo   or sameColumnName like   andOneMore

User · Answer

You could try something like this   select   from jobdetails where job no like  071 12      Not exactly what you re asking  but it has the same effect  and is flexible in other ways too

User · Answer

I had a similar goal - and came to this solution   select   from jobdetails as JD where not exists   select code from table of codes as TC                        where JD job no like TC code      I m assuming that your various codes   0711     0712    etc   including the    are stored in a table  which I m calling  table of codes   with field code   If the   is not stored in the table of codes  just concatenate the       For example   select   from jobdetails as JD where not exists   select code from table of codes as TC                        where JD job no like concat TC code            The concat   function may vary depending on the particular database  as far as I know   I hope that it helps   I adapted it from   http   us generation-nt com answer subquery-wildcards-help-199505721 html

User · Answer

How about something like this   declare  search table       searchString varchar 10     -- add whatever criteria you want    insert into  search select  0711   union select  0712    select j   from jobdetails j     join  search s on j job no like s searchString

User · Answer

How about   WHERE LEFT job no  4  IN   0711    0712

[sql] SQL Server using wildcard within IN

Examples related to sql

Examples related to sql-server

Examples related to wildcard

Examples related to in-function