PL/SQL Return specific value of a variable string

Asked

Viewed 27 times

0

I need to separate specific information from a string within a field, in case only the value "refDate:dd:mon:yy", but the string varies in each record.

How could it bring about the desired result?

String example:

(userId:353910|storeID:702|saleAreaId:|shiftId:5|refDate:30-DEC-20|classificationId:11)

SELECT* 
FROM xxxxxxxxxxxxx
where start_date between '04/01/21 00:00:00' and '20/01/21 00:00:00'
and PARAMS like '%DEC-20%'
and SUBPROC = 'save_ITEMS'

---EDITED:

I came to the following result:

SELECT
substr(params, instr(PARAMS, 'refDate')) as dt_ref 
FROM flex.flx_api_log_proc
where start_date between '04/01/21 00:00:00' and '20/01/21 00:00:00'
and PARAMS like '%DEC-20%'
and SUBPROC = 'save_ITEMS'
;

But I still need to get some information out beyond the date: refDate:30-DEC-20|classificationId:11)

sql string pl-sql

  • If I can understand your doubt you have to define how to extract the snippet from the string that interests you in the field (you say it varies from record to record) and turn it into a datetime field to use in the WHERE clause.

  • Actually I just need to remove the information corresponding to the date of the field in string form, as I showed in the question edition, now I just need to format the query to bring only the necessary. Ex: bring only the "refDate:30-DEC-20" in each row.

  • "ORA-00939: Too Many Arguments for Function"

  • (params, instr(PARAMS, 'refDate'),17) - refDate:30-DEC-20 . Solved guys, thank you very much!

1 answer

0

As @Motta mentioned The following resolution:

SELECT
substr(params, instr(PARAMS, 'refDate'),17) as dt_ref  
FROM xxxxxxxxxxxxx
where start_date between '04/01/21 00:00:00' and '20/01/21 00:00:00'
and PARAMS like '%DEC-20%'
and SUBPROC = 'save_ITEMS'

That was my first question, thank you very much to everyone who answered!

Browser other questions tagged sql string pl-sql

You are not signed in. Login or sign up in order to post.