CODEWITHSUNDEEP
mysql
PaulG
PaulG

Reputation: 7102

MySql, using LIKE to find an exact string

One of my tables has a field called MembersList, which is a list of names in the format LASTNAME/FIRSTNAME.MIDDLENAMEorMIDDLEINITIAL separated by semi-colons (;). Example:

DOE/JOHN.PETER;SMITH/ANDREW.K;FORD/HENRY.GEORGE

Now a separate record might contain the name DOE/JOHN.P, but this person may not be the same as DOE/JOHN.PETER as in the above example, his P might stand for Paul or Philip, but the user of our service has entered him as simply DOE/JOHN.P.

If I say

SELECT * FROM Events WHERE MembersList LIKE '%DOE/JOHN.P%'

I will get the record for DOE/JOHN.P as well as the record for DOE/JOHN.PETER which is not what I want. The DOE/JOHN.P in this case should be treated as if his middle initial is actually P and only P. I've tried removing the second % but it returns nothing. What should my LIKE look like??

Does this make sense??

Upvotes: 1

Views: 841

Answers (5)

Gryphius
Gryphius

Reputation: 78886

you could use regular expression instead of like to check if the name ends with a semi-colon or if the string is at the end

SELECT * FROM Events WHERE MembersList REGEXP 'DOE/JOHN\.P(;|$)'

Upvotes: 1

spencer7593
spencer7593

Reputation: 108380

Here's one approach:

SELECT * FROM Events WHERE CONCAT(';',MembersList,';') LIKE '%;DOE/JOHN.P;%'

The trick here is that we're looking for the semicolon delimiters, and we add a leading and trailing delimiter to the string we're searching.

Alternatively, you could avoid adding the leading and trailing delimiters, and test on four conditions. As a middle element in the string, the first element in the string, the last element in the string, and the only element in the string:

LIKE '%;foo;%'
LIKE 'foo;%'
LIKE '%;foo'
LIKE 'foo'

Upvotes: 2

d-_-b
d-_-b

Reputation: 23171

The % is a wildcard. so if you put it at the end of a string (Doe/John.P%) it will find anything that ends with P and ANYTHING after that P.

http://www.w3resource.com/sql/wildcards-like-operator/wildcards-like-operator.php

So if you're using the LIKE(%..%) syntax and want to differentiate between: LIKE 'DOE/JOHN.P' and LIKE 'DOE/JOHN.PETER' You can't put a wildcard after the P because that will pick up anything that has DOE/JOHN.P in the string.

%DOE/JOHN.P% will pick up both .P and .PETER

%DOE/JOHN.P will pick up anything that ends in a p

You could combine them into an or statment:

LIKE ... OR LIKE.... and do both to find both.

I would suggest using = instead.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

SELECT * 
FROM Events 
WHERE MembersList = 'DOE/JOHN.P' 
    or MembersList LIKE 'DOE/JOHN.P;%' 
    or MembersList LIKE '%;DOE/JOHN.P;%' 
    or MembersList LIKE '%;DOE/JOHN.P'

Upvotes: 2

Ian Clelland
Ian Clelland

Reputation: 44122

You'll need to test four conditions:

SELECT * FROM Events
WHERE MembersList = 'DOE/JOHN.P'
WHERE MembersList LIKE 'DOE/JOHN.P;%'
WHERE MembersList LIKE '%;DOE/JOHN.P'
WHERE MembersList LIKE '%;DOE/JOHN.P;%'

Alternately, if you can be sure that there are no commas in the string, you can use FIND_IN_SET

SELECT * FROM Events
WHERE FIND_IN_SET('DOE/JOHN.P', REPLACE(MembersList, ';', ','))

Unfortunately, FIND_IN_SET only works on comma-delimited lists, so you have to translate the semicolons to commas before using it.

All of these solutions are going to be slow. It would be better if you could maintain the members in a separate table (properly normalized), and build up the semicolon-delimited list only when you need it.

Upvotes: 2

Related Questions