CODEWITHSUNDEEP
mysqlsql
James
James

Reputation: 69

SQL Select - Where search term contains '.'

I'm trying to do an MySQL SELECT query on a table that looks a bit like this:

-------------------------------
|  id  |  domain      | etc...
|------+--------------+--------
|  1   |  dev.howmuch |
|------+--------------+--------
|  2   |  devhowmuch  |

At the moment I'm just trying to do the simple

SELECT * FROM `tbl_swad_apps` WHERE `domain` = 'devhowmuch'

Which works fine, however as soon as I try

SELECT * FROM `tbl_swad_apps` WHERE `domain` = 'dev.howmuch'

It returns no results, my guessing is that it's trying to treat the fullstop as a wildcard or as a table reference (table.field).

How can I get it to query purely as a string? Thanks

EDIT: I appreciate the help guys but none of these are working: There are no trailing or leading spaces. If I try LIKE '%howmuch%' both results turn up, if I do LIKE '%.howmuch%' nothing comes up.

hex(domain) result as requested:

1 dev.howmuch 646576A9686F776D756368
2 devhowmuch 646576686F776D756368

Upvotes: 0

Views: 77

Answers (3)

Álvaro González
Álvaro González

Reputation: 146540

The cell contains this:

d  e  v  .  h  o  w  m  u  c  h
64 65 76 A9 68 6F 77 6D 75 63 68

Full stop should probably be 2E (it's a 7-bit ASCII character so it's the same byte in many encodings, including UTF-8):

mysql> SELECT HEX('.');
+----------+
| HEX('.') |
+----------+
| 2E       |
+----------+
1 row in set (0.00 sec)

But you have A9. That's not a 7-bit ASCII character and we don't know what encoding your data uses so we can't tell what it is (but it's clearly not a dot). In ISO-8859-1 and Windows-1252 it'd be a copyright symbol (©). In UTF-8 it'd be an invalid character, typically displayed as REPLACEMENT CHARACTER (�) by many clients.

Upvotes: 2

tember
tember

Reputation: 1496

If there might be leading/trailing spaces you can do this:

SELECT * FROM `tbl_swad_apps` WHERE `domain` = LTRIM(RTRIM('dev.howmuch'))

Upvotes: 0

Alex
Alex

Reputation: 17289

You are not honest.

There is no problem with period.

Here is a proof: http://sqlfiddle.com/#!9/2e380/1

The problem should be with the real value in the table that include some nonprinting characters.

So you can choose - fix the value in the table.

Or use other query like:

SELECT * FROM `tbl_swad_apps` WHERE `domain` LIKE '%dev.howmuch%';

or even:

SELECT * FROM `tbl_swad_apps` WHERE `domain` LIKE '%dev%.%howmuch%';

http://sqlfiddle.com/#!9/2e380/4

Upvotes: 1

Related Questions