This is the same question as here, but the answers there were very specific to PHP (and I'm using Redshift SQL, not PHP).
I'm trying to remove specific suffixes from strings. I tried using RTRIM, but that will remove any of the listed characters, not just the full string. I only want the string changed if the exact suffix is there, and I only want it replaced once.
For example, RTRIM("name",' Inc') will convert "XYZ Company Corporation" into "XYZ Company Corporatio". (Removed final 'n' since that's part of 'Inc')
Next, I tried using a CASE statement to limit the incorrect replacements, but that still didn't fix the problem, since it will continue making replacements past the original suffix.
For example, when I run this:
CASE WHEN "name" LIKE '% Inc' THEN RTRIM("name",' Inc')
I get the following results:
"XYZ Association Inc" becomes "XYZ Associatio". (It trimmed Inc but also the final 'n')
I'm aware I can use the REPLACE function, but my understanding is that this will replace values from anywhere in the string, and I only want to replace when it exists at the end of the string.
How can I do this with Redshift? (I don't have the ability to use any other languages or tools here).
You could use REGEXP_REPLACE to remove the trailing Inc by using a regex that anchors the Inc to the end of the string:
CASE WHEN "name" LIKE '% Inc' THEN REGEXP_REPLACE("name", ' Inc$', '')
Note that given the regex will only match a string that ends in Inc, the CASE WHEN "name" LIKE '% Inc'is not strictly necessary as a replacement can only occur when that is true. However it may give better performance to leave it in as regex operations can be expensive time wise.
A better solution though is to keep the CASE WHEN and simply strip the last 4 characters (i.e. Inc) when it matches:
CASE WHEN "name" LIKE '% Inc' THEN LEFT("name", LENGTH("name")-4)
PostgreSQL (Redshift base) demo on dbfiddle.uk