Finding MySQL fields ending in spaces

Question

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.

can you post some line from the dump in where you are sure there are spaces? — kappa
– kappa, Commented Mar 21, 2012 at 14:16

blahblah · Accepted Answer · 2014-02-06 15:31:04Z

8

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.

answered Feb 6, 2014 at 15:31

blahblah

1061 silver badge4 bronze badges

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

Comments

kappa · Accepted Answer · 2012-03-21 14:15:03Z

7

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.

edited Mar 21, 2012 at 14:15

answered Mar 21, 2012 at 14:03

kappa

1,5798 silver badges19 bronze badges

3 Comments

kappa Over a year ago

I tested in a local table and it works.. what if you try to SELECT replace(dirtyfield,' ','--SPACE--') ? Does any extracted row contains --SPACE--?

David Faber Over a year ago

The only thing I can think of is that the spaces aren't actually spaces, but perhaps whitespace or other unprintable characters. Because the suggestions in the above answer should work. Perhaps try: SELECT * FROM mytable WHERE dirtyfield REGEXP '\s+$' (I removed the backticks to format). That would return fields ending in any whitespace (such as tab, newline, etc.).

kappa Over a year ago

so try with regex this way: ... REGEXP '[^0-9a-zA-Z]$' this will match any non alphanumeric char before end.

user1283516 · Accepted Answer · 2012-03-21 14:08:22Z

4

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 "% "

answered Mar 21, 2012 at 14:08

user1283516

412 bronze badges

3 Comments

MidnightLightning Over a year ago

Nope, zero rows :( If I do LIKE "foobar%" I get rows that have "foobar" and "foobar ", as opposed to LIKE "foobar" which only returns the 'clean' "foobar" rows. But it doesn't seem to work in reverse to pick up that space.

Leigh Over a year ago

No, the suggested query should work. Are you positive your values are really what you think they are?

Leigh Over a year ago

Never mind, I see the question was updated to confirm the values were different than expected.

Taryn · Accepted Answer · 2014-09-30 17:56:10Z

2

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

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

edited Sep 30, 2014 at 17:56

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Sep 13, 2014 at 9:03

Dev Says

212 bronze badges

Comments

Robin Castlin · Accepted Answer · 2012-03-21 14:24:19Z

1

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.

answered Mar 21, 2012 at 14:24

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Collectives™ on Stack Overflow

Finding MySQL fields ending in spaces

5 Answers 5

Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related