Update query with spaces

Question

I have an update query like this:

update table 
set col = 'hello hi' 
where col = 'hi hello'

But it is not updating the column which has the value as 'hi hello' (more than one space in between hi and hello). Any suggestions?

Answer 1

If you need to update every row with various numbers of spaces, you need to use this command instead:

update table 
set col = 'hello hi' 
where col LIKE 'hi %hello'

The % is the placeholder for "any character, any number of repetitions"

Answer 2

In Postgres you can use a regular expression for that:

update the_table
set col = 'hello hi'
where col ~ '^(hello)\s+(hi)$';

The ^ at the beginning and the $ at the end are needed to avoid updating a row where the column contains e.g. 'here hello hi' or 'hello hi there'

If there can be spaces before hello or after the hi then you can add additional wildcards:

update the_table
set col = 'hello hi'
where col ~ '^\s*(hello)\s+(hi)\s*$';

Another option would be to use trim() on the column itself with the first regular expression:

update the_table
set col = 'hello hi'
where trim(col) ~ '^(hello)\s+(hi)$';

More details about regular expressions are in the manual: http://www.postgresql.org/docs/current/static/functions-matching.html

Answer 3

To avoid the problems with wildcards

update table 
set col = 'hello hi' 
where LEFT(col, 3) = 'hi ' 
      AND
      RIGHT(col, 6) = ' hello'
      AND
      SUBSTRING(col, 3, LEN(col)-7) = REPLICATE(' ',  LEN(col)-7)