CODEWITHSUNDEEP
sqlregexoracle-databaseplsqloracle11g
Velocity
Velocity

Reputation: 479

Oracle PL SQL Procedure requires modification to accept arrays with all types of characters and split

I have a procedure PUSH_DATA which takes input data in the format:

["value 1","value 2","value 3","value n"]

(All details are available in db fiddle link at the end)

It then splits the comma separated values into database columns of the destination table.

Now, the issue is that the input string is passed by end user and he at times introduces multiples doubles quotes in between and the main splitting criteria is on basis of double quotes.

The procedure is present here in fiddle and at the end an example of string has been passed to demo the issue.

So when procedure is passed as:

BEGIN
PUSH_DATA(110,'["Project title afor BYU heads","The values are,\n "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,\n prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"]');
END;
/

Result is as expected:

enter image description here

When the string is passed with extra quotes as below:

BEGIN
PUSH_DATA(110,'[""Project title afor BYU heads"","The values are,\n "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,\n prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"]');
END;
/

Result is unexpected due to extra quotes:

enter image description here

Requirement is to ignore any special character coming inside the quotes, ignore as in it should be displayed and not evaluated, like extra double quotes or a / or a \ to be ignored and string passed as it is.

So if a value is passed like, "Hi There""s, No" The output should be Hi there""s, No.

Therefore, I require a modification to the solution such that

  1. Either use existing regex condition and add condition to include double quotes inside double quotes
  2. Or, instead of passing the parameter as varchar, use maybe array or any other solution which fits the scenario.

Fiddle with all details:

Upvotes: 3

Views: 155

Answers (1)

rootkonda
rootkonda

Reputation: 1743

(select data_string from dual),
    rcte (id, data, lvl, result)
    AS (
         SELECT p_id, data, 1,regexp_substr(data,
                '(".*?")(,|\])', 1, 1, 'n', 1) result
           FROM (select data_string data from input) 
        UNION ALL
         SELECT id, data, lvl + 1,
                regexp_substr(data, '(".*?")(,|\])', 1, lvl + 1, 'n', 1)
           FROM rcte
        WHERE lvl <= regexp_count(data, '(".*?")(,|\])',1,'n')
       )

  • Removed the regex_replace from first select query instead using data directly.

  • regexp_substr(data,'(".*?")(,|\])', 1, 1, 'n', 1) null was replaced with 'n' in order to match new line character when we use dot in regex. This regex does a lazy matching until it either finds , or ] character. ] is for the last string.

  • regexp_count(data, '(".*?")(,|\])',1,'n') changed the regex and also replaced null with 'n' for newline character match.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=5826bffbab9937d497245487faa04c60

Upvotes: 1

Related Questions