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: 110651

  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: 247180

  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?

  ---