Home » SQL & PL/SQL » SQL & PL/SQL » Replace strings [merged by jd] (Oracle RDBMS, 12.1, Windows)
Replace strings [merged by jd] [message #672756] Fri, 26 October 2018 07:40 Go to next message
unna
Messages: 9
Registered: October 2018
Junior Member
Dear all,

I have a table with these data:

id col1 col2 col3
11 1 12154 Ran into $1 error, $2, because of $3
11 2 TNS Ran into $1 error, $2, because of $3
11 3 NETWORK ERROR Ran into $1 error, $2, because of $3

I need to get a result as:
id col3
11 Ran into ora-12154 error, TNS, because of NETWORK ERROR.

Can you advise the best way to get this result?

Thanks,
Unna

Replace strings [message #672757 is a reply to message #672756] Fri, 26 October 2018 07:43 Go to previous messageGo to next message
unna
Messages: 9
Registered: October 2018
Junior Member
Dear all,

I have a table with these data:

id col1 col2 col3
11 1 12154 Ran into $1 error, $2, because of $3
11 2 TNS Ran into $1 error, $2, because of $3
11 3 NETWORK ERROR Ran into $1 error, $2, because of $3

I need to get a result as:
id col3
11 Ran into ora-12154 error, TNS, because of NETWORK ERROR.

Can you advise the best way to get this result?

Thanks,
Unna

Re: Replace strings [message #672761 is a reply to message #672757] Fri, 26 October 2018 07:49 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
This post is unreadable. Use code tags please. Based on what I see with this unreadable mess, what's wrong with

select * from table where id=11 and col1=3;
Re: Replace strings [message #672763 is a reply to message #672761] Fri, 26 October 2018 08:00 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Replace strings [merged by jd] [message #672765 is a reply to message #672756] Fri, 26 October 2018 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Replace strings [message #672773 is a reply to message #672761] Fri, 26 October 2018 08:45 Go to previous messageGo to next message
unna
Messages: 9
Registered: October 2018
Junior Member
Dear joy_division,

Thanks for your reply. I think you misunderstand my question. I try to a report with the result on col3, which has data "Ran into $1 error, $2, because of $3" to translate into "Ran into ora-12154 error, TNS, because of NETWORK ERROR."

The $1, $2, and $3 are variable numbers which can be found from col1 and their variable values are found in col2.

id col1 col2 col3
11 1 12154 Ran into $1 error, $2, because of $3
11 2 TNS Ran into $1 error, $2, because of $3
11 3 NETWORK ERROR Ran into $1 error, $2, because of $3


I am thinking of using regexp_replace with decode/case to work on it. However, I am still struggling with regexp_replace function.


Thanks,
Unna

[Updated on: Fri, 26 October 2018 08:45]

Report message to a moderator

Re: Replace strings [message #672784 is a reply to message #672773] Fri, 26 October 2018 09:23 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you're redundantly duplicating the error text over multiple rows.
You should have the message and the replacement values in different tables.
Re: Replace strings [message #672785 is a reply to message #672784] Fri, 26 October 2018 09:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
Registered: January 2010
Location: Connecticut, USA
Senior Member
As cookiemonster noted your design is not normalized. You should have message table with:


id message
11 Ran into $1 error, $2, because of $3
and message parameter table:

message_id parameter_id parameter_value
11         1            12154
11         2            TNS
11         3            NETWORK

In regards to solution, regexp is not needed here. Use plain replace over model or recursive subquery factoring.

SY.
Re: Replace strings [message #672786 is a reply to message #672785] Fri, 26 October 2018 09:50 Go to previous messageGo to next message
unna
Messages: 9
Registered: October 2018
Junior Member
Thanks for all your replies.

SY,

With you sample message and message parameter tables, can you advise a query on how to generate a result for message field as "Ran into 12154 error, TNS, because of NETWORK"?


Thanks,
Unna
Re: Replace strings [message #672792 is a reply to message #672786] Fri, 26 October 2018 10:47 Go to previous message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 26 October 2018 15:13

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
If you post what is requested we can SHOW you a query.


Previous Topic: execute immediate (merged)
Next Topic: Generate Random Id's of existing Id's
Goto Forum:
  


Current Time: Tue Sep 29 21:42:41 CDT 2020