0

I have a database table which has HTML files for a bunch of language / user profiles. I would like to list all the javascript files that are being used in each data file, to create sort of a Javascript library index.

Here's my attempt so far:

SELECT DISTINCT t.id,
   template_id,
   language_id,
   dbms_lob.substr(regexp_substr(x.data, '(src=\")+(\S)+(.js)',1,1),4000,1) javascript_file,
 FROM template t, template_language x
 WHERE t.id = x.template_id
   AND (regexp_like(x.data, '(src=\")+(\S)+(.js)', 'i'))
ORDER BY 1

The problem is that regexp_substr only returns one value, so I'm only getting one javascript per file as output even though I know that the files are filled with script tags that import javascript files. In this screenshot you can see how I only get one js file as output per file while there are more js files with different names in my data fields (clobs).

I would like to see all js files in my output for each file. Can somehow suggest a workaround please?

Thank you

Piet Smet
  • 69
  • 7
  • Is this just about string aggregation? e.g. https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle – William Robertson Feb 09 '18 at 10:32
  • Possible duplicate of [Oracle 11g get all matched occurrences by a regular expression](https://stackoverflow.com/questions/17596363/oracle-11g-get-all-matched-occurrences-by-a-regular-expression) – Kaushik Nayak Feb 09 '18 at 11:38
  • I saw that thread but the solution only works if there is only one source string, while I have multiple files with multiple lines. – Piet Smet Feb 09 '18 at 13:22
  • @WilliamRobertson no it is not. – Piet Smet Feb 09 '18 at 13:23
  • This might be worth a read: https://stackoverflow.com/questions/17596363/oracle-11g-get-all-matched-occurrences-by-a-regular-expression – highnelly Feb 09 '18 at 15:04
  • @PietSmet : Post at least one sample row from your source table and desired output for that row. Do not post images,only text data. – Kaushik Nayak Feb 09 '18 at 15:07
  • "Get all matches" can be done as in the link provided by Kaushik Nayak. If you have more than one input string, that can be accommodated easily (search this site for "split comma-separated strings" and similar to see how). Issues: (1) your CLOBs may be longer than 4000 characters, how are you dealing with that? (2) Do you or do you not want case-insensitive matching? It's one way in SELECT and the other way in WHERE. (3) You don't need the WHERE clause, it only duplicates work (and it DOES duplicate work!) –  Feb 12 '18 at 03:40

0 Answers0