3

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.

4
+50

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).

  • \s isn't "space", \s is a character class that is defined as [ \t\r\n\v\f] your \t \v \n \r are all redundant. – Evan Carroll Mar 27 '17 at 16:01
  • @EvanCarroll True, it's not just space. I couldn't find the exact definition, thus my suggestion to explicitly use all characters the OP wants to exclude. – ypercubeᵀᴹ Mar 27 '17 at 16:09
  • 2
    space is [ ] :) – Neil McGuigan Mar 27 '17 at 18:25
4

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.

1

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).

  • I'm confused about the claim that these methods detect non-breaking space (as a space). It seems they don't. Unless you aren't claiming that. – ypercubeᵀᴹ Mar 27 '17 at 8:31
  • @ypercubeᵀᴹ: I think whether they get or not non-breaking space is very system-dependent (or locale-dependent). It seems not to work the same on a Windows or a MacOS machine, for instance. – joanolo Mar 27 '17 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.