Jump to content
unna

Replacing strings

Recommended Posts

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

Share this post


Link to post
Share on other sites

Hello Unna,

Thanks for your question. The below code satisfies your requirement.

 

WITH t1 (
    id,
    col1,
    col2,
    col3
) AS (
    SELECT
        11,
        1,
        '12154',
        'Ran into $1 error, $2, because of $3'
    FROM
        dual
    UNION ALL
    SELECT
        11,
        2,
        'TNS',
        'Ran into $1 error, $2, because of $3'
    FROM
        dual
    UNION ALL
    SELECT
        11,
        3,
        'NETWORK ERROR',
        'Ran into $1 error, $2, because of $3'
    FROM
        dual
),t2 AS (
    SELECT
        MAX(id) id,
        MAX(col3) col3,
        MAX(
            CASE
                WHEN col1 = 1   THEN col2
            END
        ) attr1,
        MAX(
            CASE
                WHEN col1 = 2   THEN col2
            END
        ) attr2,
        MAX(
            CASE
                WHEN col1 = 3   THEN col2
            END
        ) attr3
    FROM
        t1
) SELECT
    id,
    replace(replace(replace(col3,'$1','ora-'
    || attr1),'$2',attr2),'$3',attr3) col3
  FROM
    t2;

 

 

Thank you,

Boobal Ganesan. Author of Advanced PLSQL Programming: The Definitive Reference

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×