Use SQL to replace last 3 characters of a string in an Oracle DB table

Do you want this in an actual UPDATE statement, or just in a column in a SELECT statement?

---

»bp

Sorry ... in an actual update statement

Okay, take a look at this, it's a basic approach (no regular expressions, etc), but should do the job.

UPDATE TABLE1
SET TEXT = 
CASE 
    WHEN SUBSTR(TEXT, -3) = '-7M' THEN SUBSTR(TEXT, 1, LENGTH(TEXT)-3) || '-10M'
    WHEN SUBSTR(TEXT, -4) = '-14M' THEN SUBSTR(TEXT, 1, LENGTH(TEXT)-4) || '-20M'
END
WHERE TEXT LIKE '%-7M' OR TEXT LIKE '%-14M';

Select allOpen in new window

---

»bp

Sorry, I think there were a few errors and omissions in my original question. I will try again and hopefully this will clarify a few things. Also, apologize for the delay in my response. I lost internet connectivity yesterday and didn't get it back until this morning.

In Oracle, I'd like to write some SQL to update records in a table by replacing the last 3 or 4 characters of a string with 3 or 4 different characters. As an example, if the last 3 characters of a string are "-7M", I want to change them to "-10M". If the last four characters are "-14M", I want to change them to "-20M". To continue that example, if the value of a field is 123456-7M, I want to change it to 123456-14M; if the value of the field is 25689-14M I want to change it to 25689-20M. Long story short ... anywhere I find "-7M" at the end of a string, I want to replace that with "10M". Anywhere I find "-14M" at the end of a string, I want to replace that with "-20M".

What I have tried thus far, without success is:

update tablea
set fielda = REPLACE(fielda , '-7M', '-10M')
where fieldb = 'DRAFT';

update tablea
set fielda = REPLACE(fielda , '-14M', '-20M')
where fieldb = 'DRAFT';

Each of the above results in a syntax error.

Any ideas on how to make this work?

Hopefully, this clarifies what it is I'm attempting to do.

My above comment #a42469009 does exactly that.

---

»bp

Using an approach like:

update tablea
set fielda = REPLACE(fielda , '-7M', '-10M')
where fieldb = 'DRAFT';

could be dangerous, since you are not checking for the replacement to happy only in the rightmost positions.  So the following changes could be made, which might not be wanted:

123456-7MX => 123456-10MX
123456-7M-12B => 123456-10M-12B

---

»bp

Hi Bill,

I must have missed your original post yesterday describing the "case" method. Sorry about that. I am confident that it will work but have not yet been able to try it as my connection to the database was severed. I will get back to it tonight, give it a shot, and let you know how things go.

Thanks for your assistance.

Bill, I totally agree with you as I, too, have made assumptions based on an asker's statements only to find out the issues were entirely different. At any rate, in my original post I said, "If it's always and only -7 and -14 that matter and only one hyphen, just use a nested  replace" in the hopes that I would get a response as to whether or not that was true. Without any further clarification or complete knowledge of the data in question, your solution works fine and is most likely the safest :-)

Thanks everyone for assisting on this. Sorry I haven't been more communicative but I'm struggling in a location with very spotty internet coverage. I will get back to all of you this evening