Find rows in which the column doesn't contain a "space"

Question

I'm using Postgres 9.5. I want to search for rows in which my name column does not contain a space. I'm a little murky on how to define a space to you, though. I thought it would just be the space bar on my keyboard, so I ran:

.... where name not like '% %';

but then I got some results like this:

 | JASON FALKNER

That sure looks like a space to me, but there are probably some other things going on. Is there a better way I can scan for rows in which my name column doesn't contain a space?

Using a regexp, not (name ~ '\s') still returned columns that looked like they had a space.

Using:

select cast(name as bytea) ... where name not like like '% %';

returned:

\x4a41534f4ec2a0424c414b45

However, I'm still a little unclear how I use that data to figure out how to screen spaces from my results.

I tried where not (name ~ '[[:space:]]')' and it's returning "JASON BLAKE" with the same byte sequence above, \x4a41534f4ec2a0424c414b45.

Answer 1

I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex:

where name !~ '[ \t\v\b\r\n\u00a0]'

Characters:

\s         white space (space, \r, \n, \t, \v, \f)
' '        space
\t         (horizontal) tab
\v         vertical tab
\b         backspace
\r         carriage return
\n         newline
\f         form feed
\u00a0     non-breaking space
---

See the docs for pattern matching.

In your example, note that \xC2A0 is the UTF-8 representation of Non breaking space (00A0).

Answer 2

There exist a multitude of space characters as per the following Unicode listing:

Unicode Characters in the 'Separator, Space' Category

I would extend the where clause in ypercubeᵀᴹ's answer to:
(edit: added \ at the beginning of the string]

where name !~ '[\u0020\u00A0\u1680\u2000\u2001\u2002\u2003\u2004\u2005\u2006\u2007\u2008\u2009\u200A\u202f\u205f\u3000]'

Characters:

u0020 SPACE
u00A0 NO-BREAK SPACE
u1680 OGHAM SPACE MARK
u2000 EN QUAD
u2001 EM QUAD
U2002 EN SPACE
u2003 EM SPACE
u2004 THREE-PER-EM SPACE
u2005 FOUR-PER-EM SPACE
u2006 SIX-PER-EM SPACE
u2007 FIGURE SPACE
u2008 PUNCTUATION SPACE
u2009 THIN SPACE
u200A HAIR SPACE
u202f NARROW NO-BREAK SPACE
u205f MEDIUM MATHEMATICAL SPACE
u3000 IDEGRAPHIC SPACE

.... and test and test again.

Answer 3

I tried where not (name ~ '[[:space:]]')' and it's returning "JASON BLAKE" with the same byte sequence above, \x4a41534f4ec2a0424c414b45.

I'll assume that byte sequence is UTF8:

SELECT
  string,
  string NOT LIKE '% %' AS simple,
  string ~ '^\S*$' AS regexp_oppr,
  string !~ '[[:space:]]' AS regexp_oppr_posix
FROM ( VALUES
  ('THIS HAS A SPACE IN THE MIDDLE'),
  ('   BEFORE'),
  ('AFTER    '),
  ('NONE_NONE_NONE'),
  (' | JASON FALKNER'),
  (convert_from('\x4a41534f4ec2a0424c414b45'::bytea, 'UTF8'))
) AS t(string);

That outputs this

             string             | simple | regexp_oppr | regexp_oppr_posix 
--------------------------------+--------+-------------+-------------------
 THIS HAS A SPACE IN THE MIDDLE | f      | f           | f
    BEFORE                      | f      | f           | f
 AFTER                          | f      | f           | f
 NONE_NONE_NONE                 | t      | t           | t
  | JASON FALKNER               | f      | f           | f
 JASON BLAKE                    | t      | t           | t
(6 rows)

If I was right to assume the JASON BLAKE is utf8 then all of the methods mentioned detect the space in it (and the method of ^\S*$ which is my own addition).