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?
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
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加