CODEWITHSUNDEEP
mysql
MidnightLightning
MidnightLightning

Reputation: 6928

Finding MySQL fields ending in spaces

After a database import, there's some rows with dirty fields that end in (sometimes multiple) spaces saved in the database, and in the interest of finding them amidst the many thousands of other rows, I'm trying to do a query like:

SELECT * FROM `mytable` WHERE `dirtyfield` REGEXP ' $'

But that returns zero rows. I tried a few other variations with other results:

SELECT * FROM `mytable` WHERE `dirtyfield` REGEXP '[[:space:]]$' -- Zero Rows
SELECT * FROM `mytable` WHERE `dirtyfield` REGEXP '[[.space.]]$' -- Zero Rows
SELECT * FROM `mytable` WHERE `dirtyfield` REGEXP '[[.space.]]' -- Those with a space anywhere in the value
SELECT * FROM `mytable` WHERE `dirtyfield` REGEXP '[[.space.]]{2}' -- Those with two spaces in a row

Finding all with a single space doesn't help much, since some clean rows have single spaces between words in that field. That last one catches 90% of the dirty rows, but misses those that have just a single space at the end. Is there something wrong with how I'm using the $ symbol to indicate the end of a field?

The MySQL RIGHT() and SUBSTRING() functions seem to strip off whitespace when calculating the end of the field:

SELECT * FROM `mytable` WHERE RIGHT(`dirtyfield`)=" " -- Only returns one row that has " " for that field
SELECT * FROM `mytable` WHERE SUBSTR(`dirtyfield`,-1)=" " -- Only returns one row that has " " for that field

One other try using a comparison didn't work either:

SELECT * FROM `mytable` WHERE TRIM(`dirtyfield`)!=`dirtyfield` -- zero rows returned

The "dirtyfield" field is a VARCHAR(128), if that matters.

EDIT: I'm an idiot; the fields don't end in spaces, the fields end in multiple spaces followed by a comma (imported from a bad CSV file).

SELECT * FROM `mytable` WHERE RIGHT(`dirtyfield`,1)=','

That query found them. I was looking at the output of the tables in a comma-separated view and didn't notice the commas were doubled-up.

Upvotes: 4

Views: 10688

Answers (5)

Dev Says
Dev Says

Reputation: 21

Hey i was doing something similar and got in this page, Check below query, this might help..

SELECT * FROM `mytable` WHERE `dirtyfield` LIKE "% %"

Upvotes: 2

blahblah
blahblah

Reputation: 96

Had a similar problem. The compare you made with trim():

SELECT * FROM `mytable` WHERE TRIM(`dirtyfield`)!=`dirtyfield`

doesnt work, but:

SELECT * FROM mytable where char_length(dirtyfield) > char_length(trim(dirtyfield))

gets the work done and shows you the rows that have spaces both at the start and/or end of the content. The character count works. Quite honestly i don't know why trim() doesn't compare directly on the first query.

Hope this helps. Like your field, this solution is admittedly a bit dirty.

Upvotes: 7

Robin Castlin
Robin Castlin

Reputation: 10996

Have you considered that you actually get rows with spaces in it, but the web browser doesn't render them for you?

The value " two (2 or more spaces) words " looks like "two words" in your browser.

Try a:

WHERE dirtyfield REGEXP '(^[[:space:]]|[[:space:]]{2,}|[[:space:]]$)'

This should fetch all of those rows. Just consider that you might get the right rows, but it doesn't look that way due to browser.

Upvotes: 1

kappa
kappa

Reputation: 1569

I think this may be correct:

SELECT * FROM `mytable` WHERE `dirtyfield` REGEXP '[[.space.]]+$'

It matches field ending ($) with 1 or more (+) spaces ([[.space.]]) Anyway if you want to do the same thing with LIKE statement is easier with:

... LIKE '% '

as in answer below.

Upvotes: 7

user1283516
user1283516

Reputation: 41

I have not tried this but I think this might work to find entries that ends with a blank space

SELECT * FROM `mytable` WHERE `dirtyfield` LIKE "% "

Upvotes: 4

Related Questions