June 15, 2018 at 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
June 15, 2018 at 9:10 am
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)
June 15, 2018 at 11:01 am
jeffshelix - Friday, June 15, 2018 8:50 AMI 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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply