CODEWITHSUNDEEP
mysqlwildcardvarcharmysql-8.0where-in
surfmuggle
surfmuggle

Reputation: 5944

SELECT WHERE in varchar with wildcard

Lets say i have a table like this

CREATE TABLE Parts (ID int, part_number varchar(100), isActive TINYINT);

and these sample records

|ID | part_number   | isActive|
===============================
  1 | 1N3.805.327   |  1      
  2 | 1N3.805.327.B |  1
  3 | 1N3.804.108.B |  1
  4 | 1N3.804.108.C |  1
  5 | 1N3.804.107.B |  1
  6 | 1N3.804.107.C |  1
  7 | 1N3.804.106.A |  1
  8 | 1N3.804.105.A |  1

Problem

I would like to combine a where in clause with the wildcard % operator In my dbfiddle sample i tried the string function find_in_set and the comparison operator in(). Both do not work:

-- without wildcard the query works
SELECT * FROM Parts WHERE part_number in ('1N3.804.108.B', '1N3.804.106.A'); -- 2

-- with wildcard no records are returned
SELECT * FROM Parts WHERE part_number in ('1N3.804.108%', '1N3.804.106%'); -- 0 
SELECT * FROM Parts WHERE FIND_IN_SET(part_number, '1N3.804.108%,1N3.804.106%'); -- 0

Questions

  1. I assume i could use WHERE LEFT(part_number, 11) in ('1N3.804.108', '1N3.804.106') But i do not know if this has any disadvantages.
  2. Is there a way to use a wildcard operator with in()?

Sample records

INSERT INTO 
    Parts(ID, part_number, isActive)
VALUES
    (1, '1N3.805.327',1),
    (2, '1N3.805.327.B',1),
    (3, '1N3.804.108.B',1),
    (4, '1N3.804.108.C',1),
    (5, '1N3.804.107.B',1),
    (6, '1N3.804.107.C',1),
    (7, '1N3.804.106.A',1), 
    (8, '1N3.804.105.A',1);

Upvotes: 0

Views: 100

Answers (2)

nbk
nbk

Reputation: 49395

Use REGEXP for that, when you want to use OR

CREATE TABLE Parts (ID int, part_number varchar(100), isActive TINYINT);
INSERT INTO 
    Parts(ID, part_number, isActive)
VALUES
    (1, '1N3.805.327',1),
    (2, '1N3.805.327.B',1),
    (3, '1N3.804.108.B',1),
    (4, '1N3.804.108.C',1),
    (5, '1N3.804.107.B',1),
    (6, '1N3.804.107.C',1),
    (7, '1N3.804.106.A',1), 
    (8, '1N3.804.105.A',1);

✓

✓

SELECT * FROm Parts WHeRE part_number REGEXP '^(1N3.804.108|1N3.804.106)'

ID | part_number   | isActive
-: | :------------ | -------:
 3 | 1N3.804.108.B |        1
 4 | 1N3.804.108.C |        1
 7 | 1N3.804.106.A |        1

MySQL Can only UNION a certain number of tables. i think it is about 53.

With an index on partnumber, this will be the fastest.

SELECT * FROm Parts WHeRE part_number REGEXP '^1N3.804.108'
UNION all
SELECT * FROm Parts WHeRE part_number REGEXP '^1N3.804.106'

ID | part_number   | isActive
-: | :------------ | -------:
 3 | 1N3.804.108.B |        1
 4 | 1N3.804.108.C |        1
 7 | 1N3.804.106.A |        1

SELECT * FROm Parts WHeRE part_number LIKE '1N3.804.108%'
UNION all
SELECT * FROm Parts WHeRE part_number LIKE '1N3.804.106%'

ID | part_number   | isActive
-: | :------------ | -------:
 3 | 1N3.804.108.B |        1
 4 | 1N3.804.108.C |        1
 7 | 1N3.804.106.A |        1

db<>fiddle here

Upvotes: 1

trillion
trillion

Reputation: 1401

you can also try to do

select *, 
       REGEXP_LIKE(part_number,[pattern]) as pattern_test from Parts 
where pattern_test is TRUE

here first you will create the pattern that you are interested in and apply it on the column you wish to apply it to. The pattern_test will return true if it matches with your pattern and then you can filter on that [where clause]

Upvotes: 0

Related Questions