CODEWITHSUNDEEP
mysqlsql
JonZ
JonZ

Reputation: 149

Finding exact value in mysql

I'm trying to solve problem how to find exact value from string.

Database

The problem is then searching in Column StringB for the value 1, it finds all rows containing 1. The idea is that if I look for value 1 in StringB it should only find where value is exact.

Using LIKE is not a perfect option since it will take all rows which contains 1, using = also is not a option since it searches for equal value. Also tried to use INSTR, but it works almost same as LIKE. Same with Locate.

There is currently stored formats:

And they don't change.

This column only stores numbers, no letter or other type of string ONLY numbers (integer type)

Is there any way to strictly search for value?

My database is InnoDB. Thank you for your time.

Upvotes: 0

Views: 1473

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521979

Try using REGEXP:

SELECT *
FROM yourTable
WHERE CONCAT('.', StringB, '.') REGEXP CONCAT('[.]', '2', '[.]');

Demo

We could also use LIKE instead of REGEXP:

SELECT *
FROM yourTable
WHERE CONCAT('.', StringB, '.') LIKE CONCAT('%.', '2', '.%');

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you do:

where stringB = 1

Then MySQL has to figure out what types to use. By the rules of SQL, it will convert '1.00' to a number -- and they match.

If you do

where stringB = '1'

Then the types do what you intend. And the values are compared as strings.

More: Keep the types consistent. Don't ever depend on implicit conversion.

Upvotes: 2

Related Questions