CODEWITHSUNDEEP
sqloracle-databaseplsqluser-defined-types
GMan1973
GMan1973

Reputation: 319

Transforming Strings

I need to accept the following strings and put them into a type collection and pass it to a procedure

String Cars = Dodge Charger||Ford Mustang||Chevy Camro||Ford GT
String Cost = 35,000||25,000||29,000|55,000
String CarDesc = Power House||Sweet Ride||Too Cool||Blow your door off

How do I transform the records so they will be like the following?

 Cars:
  Dodge Charger||35,000||Power House
  Ford Mustang||25,00||Sweet Ride
  Chevy Camro||29,000||Too Cool
  Ford GT||55,000||Blow your door off

How do I parse them into an array?

The types:

 create or replace TYPE             "CAR_OBJ"                                          
  AS
  OBJECT (CAR_NAME VARCHAR2 (50),
       Price    Number,
       CarDesc VARCHAR2 (100));
/
create or replace TYPE             "CAR_IN_ARR"                                          
  IS
   TABLE OF CAR_OBJ;
/


  procedure car_values (
      p_in_upd_car              car_in_arr,
      p_out_upd_results    out  car_out_cur
  )
  as

I have tried all kinds of for loops and I just can't get it in the right order

Thank you soo much

Upvotes: 0

Views: 53

Answers (1)

William Robertson
William Robertson

Reputation: 15991

The double delimiter || makes this hard, so I cheated by replacing them with ~. Probably there is a neater way to handle this with a single regex, or a more elaborate approach using substr and instr.

Also I've assumed the cost example should be 35,000||25,000||29,000||55,000.

The real code should probably confirm that all the strings contain the same number of delimiters. Also you might want to parse the cost value into a number.

declare
    inCars    long := 'Dodge Charger||Ford Mustang||Chevy Camro||Ford GT';
    inCost    long := '35,000||25,000||29,000||55,000';
    inCarDesc long := 'Power House||Sweet Ride||Too Cool||Blow your door off';

    type varchar2_tt is table of varchar2(50);

    cars      varchar2_tt := varchar2_tt();
    costs     varchar2_tt := varchar2_tt();
    carDescs  varchar2_tt := varchar2_tt();
begin
    inCars := replace(inCars,'||','~');
    inCost := replace(inCost,'||','~');
    inCarDesc := replace(inCarDesc,'||','~');

    cars.extend(regexp_count(inCars,'~') +1);
    costs.extend(regexp_count(inCost,'~') +1);
    carDescs.extend(regexp_count(inCarDesc,'~') +1);

    for i in 1..cars.count loop
        cars(i) := regexp_substr(inCars,'[^~]+', 1, i);
        costs(i) := regexp_substr(inCost,'[^~]+', 1, i);
        carDescs(i) := regexp_substr(inCarDesc,'[^~]+', 1, i);

        dbms_output.put_line(cars(i) || '||' || costs(i) || '||' || carDescs(i));
    end loop;
end;

Upvotes: 1

Related Questions