-2

Using classic ASP and MySql database. I want to parse a field that contains a list of tags, separated by a comma, and take those results and input into another table. So if I had tea,coffee,bread,milk in a field, it would parse that field and give me

tea

coffee

bread

milk

then upload the list into a different table

Once I get the parsed items, I can handle uploading it, I just need help parsing it, I think.

I have searched parsing, but I get confused very easily. I've had a few strokes and my brain doesn't work like it used to.

Any guidance will be appreciated Thanks, Penguin

Please keep in mind that I am using classic ASP with MySQL database. Not sure if that information helps or not.

Penguin
  • 13
  • 3

1 Answers1

0

In MariaDB, you can try something like this:

WITH RECURSIVE cte AS (
SELECT 1 AS ctr, MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) AS fLen FROM mytest 
  UNION ALL
SELECT ctr+1, fLen FROM cte WHERE ctr+1 <= fLen
  )
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(vals,',',ctr),',',-1) AS valsAsRows
  FROM cte
 CROSS JOIN mytest
  GROUP BY valsAsRows;

First step is to get the total of value separated by comma by doing LENGTH(column_name)-LENGTH(REPLACE(column_name,',',''))+1. In your example

tea,coffee,bread,milk

will give LENGTH(column_name)=21 and LENGTH(REPLACE(column_name,',','')=18 since we've replace the comma with nothing. That give us 21-18=3 but the correct word count is 4, which is why I added +1 at the end. Then, using RECURSIVE cte to generate row numbering based on the result. To make the comma separated values with it's own row, we CROSS JOIN the cte with main table, use SUBSTRING_INDEX() with comma as delimiter and the generated row numbering to get the position of the value.

Here's a fiddle with tests: https://dbfiddle.uk/hq-et3Cm

FanoFN
  • 6,815
  • 2
  • 13
  • 33
  • Throwing error Syntax error /stories/TEST2.ASP, line 5 WITH RECURSIVE cte AS ( here is the code <% WITH RECURSIVE cte AS ( SELECT 1 AS ctr, MAX(LENGTH(tags_)-LENGTH(REPLACE(tags_,',',''))+1) AS fLen FROM stories UNION ALL SELECT ctr+1, fLen FROM cte WHERE ctr+1 <= fLen ) SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tags_,',',ctr),',',-1) AS valsAsRows FROM cte CROSS JOIN mytest GROUP BY valsAsRows; %> – Penguin Aug 28 '23 at 01:33
  • Did you try running the query on MySQL tool? Like Workbench or HeidiSQL or SQLyog? – FanoFN Aug 28 '23 at 01:40
  • Try if this query can work : https://dbfiddle.uk/yp1I0864 – FanoFN Aug 28 '23 at 01:45
  • This was successful when I ran in phpmyadmin – Penguin Aug 28 '23 at 03:48
  • So what do I need to do to upload the valsAsRows results into a table named STORY_TAGS ?? I am just not used to running queries within the database itself. – Penguin Aug 28 '23 at 03:59
  • Wait, did you not change the table named `mytest` in the query with your own table? – FanoFN Aug 28 '23 at 04:03
  • I did change, that's why it worked. – Penguin Aug 28 '23 at 04:05
  • I see, `mytest` should be your own table name. I failed to mention that in my answer, my bad. So, does it work now in your asp code? – FanoFN Aug 28 '23 at 04:23
  • Negative. I am not sure how to convert that query so It works on an ASP page. – Penguin Aug 28 '23 at 12:42
  • I'm not familiar with ASP so I'm not sure either. I'm sure that the query can run directly from the database so, maybe you can search how to use it in ASP? or post a new question if you can't find similar issue. – FanoFN Aug 29 '23 at 02:07