CODEWITHSUNDEEP
sqlregexstringoracle-databaseclob
mac
mac

Reputation: 21

finding if a line starts with a specified string in a clob and then extract

I have a CLOB column that I want to search for a line that starts with '305' then extract something from that line, some of my rows will have multiple lines that start with '305' or '305 somewhere in the entire cell, so I'd only want to find the first line where it starts with '305' the entire cell content is split into lines like this

301|10500000908|
302|20171021|20171104|
303|00001|8306.7|
302|20171008|20171020|
303|00001|13174.5|
302|20170704|20171007|
303|00001|2508.7|
302|20170419|20170703|
303|00001|6962.9|
302|20170330|20170418|
303|00001|7628.2|
302|20170305|20170329|

--- my instr(dbms_lob.substr(flow_data, 4000, 1 ),'305|', 1, 1) keeps finding this line

303|00001|8489.1|
302|20170120|20170304|
303|00001|1997.9|
302|20161021|20170119|
303|00001|12359.8|
302|20160722|20161020|
303|00001|7354.0|
302|20160516|20160721|
303|00001|26.4|
304|20171105|
305|00001|5936.1|    


--- i want to find this line and then extract the '5936.1' from it

304|20171021|
305|00001|5710.4|
304|20171008|
305|00001|5163.1|
304|20170704|
304|20170419|
305|00001|7390.8|
304|20170330|
305|00001|7363.2|
304|20170305|
305|00001|7181.4|
304|20170120|
305|00001|9200.2|
304|20161021|
305|00001|4791.3|
305|00001|2877.5|
304|20160516|
305|00001|4116.9|
306|0393|20160511|
307|SUPP|20160511|
310|A|20160511|
311|E|20160516|

when I use instr(dbms_lob.substr(flow_data, 4000, 1 ),'305|', 1, 1) it keeps finding the wrong line. by the way there are no gaps between the lines, I inserted them to keep the text separated.

Thanks all

Mac

Upvotes: 1

Views: 542

Answers (1)

GMB
GMB

Reputation: 222462

If I follow you correctly, you can use regexp_substr():

select regexp_substr(flow_data, '^305\|[^|]*\|([^|]*)', 1, 1, 'm', 1) as val
from t

Argument breakdown:

  • flow_data: the value to search (CLOBs are allowed)

  • '^305\|[^|]*\|([^|]*)': the regex. We search for 305 at the beginning of a line, and capture the third value in the CSV list

  • 1: start the search at the beginning of source string

  • 1: return the first match

  • m - multiline mode : ^ matches at the begin of each line

  • 1: return the first captured part of the match

Upvotes: 2

Related Questions