sqlregexreplacehanainsert-update

Replacing the column values based on Regex matching SQL

I have my HANA SQL query

select 
  replace_regexpr('[ \t]+$' in "id" with '' OCCURRENCE ALL) 
from TABLE1

that will display the replaced trailing whitespaces for the column ID without updating the original values in the table.

How can I extend this query to actually save these replaced values back to the table corresponding to the respective IDs?

Solution 
  • update TABLE1
set "id" = replace_regexpr('[ \t]+$' in "id" with '' OCCURRENCE ALL) 
WHERE "id"  LIKE_REGEXPR ' [ \t]+$'

    This will set values of column "id" based on the previous value in this column.