using wildcards in and and an IN in a Where clause

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  June 15, 2018 at 8:50 am

  #413884

  I have 500 strings that i need to do a wildcard on.
  example:  PARTNUM LIKE '%abcde%'
  but i have to find 500 different PartNums.
  Obviosly i can't do  IN ('%abcde%','%rthyg%','%UYTR%')
  How can i search for my list of partnums in on statement?

  thanks

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 15, 2018 at 9:10 am

  #1994129

  How about something like putting all those search strings in a table?
  Here's a sample idea:
  DECLARE @ls AS TABLE (
    STRING varchar(10) NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @ls (STRING)
    VALUES    ('%abcde%'),
            ('%rthyg%'),
            ('%UYTR%');

  WITH SOME_PARTS AS (

      SELECT '123-abcde-456' AS PART_NO
    UNION ALL
    SELECT '123-rwstr-589'
    UNION ALL
    SELECT '123-rthyg-1004'
    UNION ALL
    SELECT '234-UYTR-5001'
    UNION ALL
    SELECT '456-AVEFE-101'
)
SELECT DISTINCT SP.PART_NO
FROM SOME_PARTS AS SP
    CROSS APPLY (
        SELECT STRING
        FROM @ls
        ) AS S
WHERE SP.PART_NO LIKE S.STRING
ORDER BY SP.PART_NO;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  June 15, 2018 at 11:01 am

  #1994163

  jeffshelix - Friday, June 15, 2018 8:50 AM

  I have 500 strings that i need to do a wildcard on.
  example:  PARTNUM LIKE '%abcde%'
  but i have to find 500 different PartNums.
  Obviosly i can't do  IN ('%abcde%','%rthyg%','%UYTR%')
  How can i search for my list of partnums in on statement?

  thanks

  Steve's idea is a good one.

  An alternative approach might be to strip all of the part numbers out of the PARTNUM column. What is the format of this column?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.