MySQL search for value containing spaces returns empty result (no rows)

Question

I have a MySQL database with a column containing part numbers. Some of the part numbers contain spaces:

3864205010  J

When I query the database or search for the part in phpMyAdmin no results are returned.

Yet when I delete the 2 spaces and then type them again, the search returns a result.

This query does not return a result:

SELECT * 
FROM  `parts` 
WHERE  `part_no` LIKE  '3864205010  K'

This query returns the result:

SELECT * 
FROM  `parts` 
WHERE  `part_no` LIKE  '3864205010  K'

They look the same but in the second query I have deleted the 2 spaces before "K" and typed the spaces again.

Actually just paste it in... someone else (even me) will format it for you if you have no idea. — d'alar'cop
– d'alar'cop, Commented Apr 11, 2013 at 12:16
this is probably not a space but a HTAB (ascii code 9) or even a line feed/carriage return (10 and 13). Copy paste in a good text editor, you'll see — Sebas
– Sebas, Commented Apr 11, 2013 at 12:39

Narek · Accepted Answer · 2013-04-11 12:42:33Z

2

If you can use wildcard instead of spaces:

SELECT * 
FROM  `parts` 
WHERE  `part_no` LIKE  '3864205010%K'

answered Apr 11, 2013 at 12:42

Narek

3,8235 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

d'alar'cop Over a year ago

If I could give more than +1 I would. spot on buddy

Sebas · Accepted Answer · 2013-04-11 13:11:30Z

This is probably not a space but a HTAB (ascii code 9) or even a line feed/carriage return (10 and 13). Copy paste in a good text editor, you'll see what it really is.

Now, regarding to your wonder about why it doesn't work even if it does look like a space, this is because every single character we see is interpreted by the engine (notepad, phpmyadmin, firefox... any software with text rendering)

What actually happens is that when the engine finds an ascii code, it transforms it into a visible character. The CHAR(9) for example is often transformed into a 'big space' usually equal to 2 or 4 spaces. But phpmyadmin might just decide to not do it that way.

Other example is the line feed (CHAR(10)). In a text editor it would be the signal that the line ends, and (under unix systems mostly) a new line has to start. But you can copy this line feed into a database field, you're just not sure about how it is going to render. Because they want you to see everything in the cell they may choose to render it as a space... but that's NOT a space if you look at the ascii code of it (and here there's no trick, all rendering engines will tell you the right ascii code).

This is important to always treat characters with their ascii codes.

there's an answer above that suggests using a wildcard instead of the spaces. That might match, or just might not. Let's say your string is '386420K5010', so it is not the one you're looking for, still the LIKE '3864205010%K' pattern would return it. The best is probably to use a regular expression or at least identify the fixed pattern of these strings.

ill probably clean up the data in my data database, just replace all <space><non-space> with <space><space>

liyakat · Accepted Answer · 2013-04-11 12:37:30Z

0

yes as updated question if you wish to remove more space between which contents might be 3 or 4 space below query will use full to you

SELECT REPLACE( REPLACE( part_no, " ", " " ), " ", " " ) from parts.

let me know if it is work for you ?

answered Apr 11, 2013 at 12:37

liyakat

11.9k2 gold badges42 silver badges47 bronze badges

Comments

Ejaz · Accepted Answer · 2013-04-11 12:45:31Z

0

SELECT * 
FROM  `parts` 
WHERE  REPLACE(REPLACE(`part_no`, CHAR(9), ''),' ','') LIKE  REPLACE(REPLACE('3864205010  K', CHAR(9), ''),' ','')

This will probably work if part_no and/or search string has tabs and/or spaces.

answered Apr 11, 2013 at 12:45

Ejaz

8,8723 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

MySQL search for value containing spaces returns empty result (no rows)

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related