CODEWITHSUNDEEP
mysqlselect
Conrad
Conrad

Reputation: 437

MySQL SELECT row based on prefix

Im attempting to do a SELECT on data in a table that contains prefixes, and I have the "keyword".

So unlike a normal search where the prefix/keyword is contained in the row data, and that the prefix/keyword is shorter (or contained in) a possible row of data.

This is the opposite. If have prefixes in rows and I want to find the best matching row based on the (longer) word/phase. 

CREATE TABLE table1 (id INT NOT NULL AUTO_INCREMENT, keyCode VARCHAR(45) NULL, Username VARCHAR(45) NULL, PRIMARY KEY (id));

INSERT INTO table1(keyCode,Username)VALUES('123','Peter')
INSERT INTO table1(keyCode,Username)VALUES('456','Paul')
INSERT INTO table1(keyCode,Username)VALUES('1234','John')

Now let's say the phrase I have been given is longer than the prefix in the data.

Like this:

SELECT * FROM table1 WHERE keyCode LIKE '123456%';

I know this will not work, but I would like to return the Row with User 'John'.

How? (I can use stored procedures)

Upvotes: 1

Views: 1681

Answers (2)

You Old Fool
You Old Fool

Reputation: 22940

You can check for the existence of the keyCode prefix at the beginning of your string using LOCATE(). For example:

SELECT *
FROM table1
WHERE LOCATE(keyCode,'123456') = 1;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

You may phrase your LIKE expression in the reverse order:

SELECT *
FROM table1
WHERE '123456' LIKE CONCAT(keyCode, '%');

This would compare, for example, '123456' against '1234%', which should be a match in this case.

Upvotes: 4

Related Questions