CODEWITHSUNDEEP
mysql
jerrygarciuh
jerrygarciuh

Reputation: 21988

Non-visible data in field

The following queries have no results:

SELECT * FROM `personal_interest` WHERE LENGTH(`interest`) = 0

SELECT * FROM `personal_interest` WHERE TRIM(`interest`) = ''

SELECT * FROM `personal_interest` WHERE `interest` = ''

However this query

SELECT * FROM  `personal_interest` WHERE person = 380298

has these results:

phpMyAdmin screenshot

// Edit:

SELECT HEX(interest) FROM personal_interest WHERE id = 19496

Result:

phpMyAdmin screenshot 2

What could be in the field that my queries do not select it? What query should I be using?

Upvotes: 2

Views: 74

Answers (1)

eggyal
eggyal

Reputation: 125865

By default, TRIM() only removes spaces. The field could contain other whitespace (such as a tab or newline character) or other non-printing characters. To see the binary-encoding of the field's content, you can use:

SELECT HEX(interest) FROM personal_interest WHERE id = 19496

As you can see from the result, your field contains 0x0d, which is a carriage return character. To select all records that are either blank or contain only whitespace characters, you can use a regular expression:

SELECT * FROM personal_interest WHERE interest RLIKE '^[[:space:]]*$'

Upvotes: 3

Related Questions