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

In Oracle, I'd like to write some SQL to replace the last 3 characters of a string with 3 or 4 different characters. So if the last 3 characters of a string are "-7M", I want to change them to "-10M". If the last two characters are "-14M", I want to change them to "-20M". For example, the if the value of the field is 123456-7M, I want to change it to 123456-14M or if the value of the field is 25689-14M I want to change it to 25689-20M.

I've googled this every way I can think of but can't find an answer.

Can someone give me a hand with this?

Thanks in advance
cbridgmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Do you want this in an actual UPDATE statement, or just in a column in a SELECT statement?


»bp
0
cbridgmanAuthor Commented:
Sorry ... in an actual update statement
0
Bill PrewIT / Software Engineering ConsultantCommented:
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';

Open in new window


»bp
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

awking00Information Technology SpecialistCommented:
Is it always a -7 gets changed to a -10 and a -14 to a -20 or is there some underlying logic like whatever number follows the '-' round it up to the nearest number evenly divisible by 10? If it's always and only -7 and -14 that matter and only one hyphen, just use a nested  replace -
update yourtable set yourstring = replace(replace(yourstring,'-7M','-10M'),'-14M','-20M')
where yourstring like '%-7M' or yourstring like '%-14M'
0
cbridgmanAuthor Commented:
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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
My above comment #a42469009 does exactly that.


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0
awking00Information Technology SpecialistCommented:
Just need to add where condition for only records ending with -7M or -14M -
UPDATE tablea
set fielda = REPLACE(REPLACE(fielda,'-7M','-10M'),'-14M','-20M')
where fieldb = 'DRAFT'
and (fielda like '%-7M' or fielda like '%-14M');
0
cbridgmanAuthor Commented:
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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Just need to add where condition for only records ending with -7M or -14M -
UPDATE tablea
set fielda = REPLACE(REPLACE(fielda,'-7M','-10M'),'-14M','-20M')
where fieldb = 'DRAFT'
and (fielda like '%-7M' or fielda like '%-14M');
That makes an assumption that the data will never contain a -7M or -14M anyplace other than the right end of the data.  I decided not to make that assumption and play it safe protecting against something like:

123456-7M-7M => 123456-10M-10M

I guess my gray hair is showing, I've been burned in the past by users that said the data will always look a certain way, only to later find out a piece of code broke when that turned out not to be the case.


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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 :-)
1
cbridgmanAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.