Replace strings [merged by jd] [message #672756] |
Fri, 26 October 2018 07:40 |
|
unna
Messages: 12 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 |
|
unna
Messages: 12 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 |
joy_division
Messages: 4963 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 #672773 is a reply to message #672761] |
Fri, 26 October 2018 08:45 |
|
unna
Messages: 12 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 |
cookiemonster
Messages: 13925 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 |
Solomon Yakobson
Messages: 3275 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 #672792 is a reply to message #672786] |
Fri, 26 October 2018 10:47 |
|
Michel Cadot
Messages: 68658 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.
|
|
|