-1

I have some values/ids coming from external sources like logs that needs to be sanitized/type casted before use. So e.g. if I have values like this:

800, 776, 7803, 827, 91, 976, 908

I want to be able to perform some operation on each value in mysql. I was hoping to be able to do something like this:

Select myCol*10
FROM (SELECT (800, 776, 7803, 827, 91, 976, 908) as myCol from dual);

but it fails with an error

ERROR 1241 (21000): Operand should contain 1 column(s).

The closest query I could find that allowed selecting multiple rows was this. But that would require prefixing each value with UNION ALL SELECT e.g.

SELECT 1, 2, 3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9

Another option that could work is in memory table or even something like this. But those seems like overkill for something seemingly so simple.

M-Wajeeh
  • 17,204
  • 10
  • 66
  • 103
  • What is **precise** MySQL version? What is max. values amount per CSV string? – Akina Jun 07 '21 at 09:38
  • @Akina version: 5.7.30, max values are like 1000. – M-Wajeeh Jun 07 '21 at 09:43
  • For your version - iterative stored procedure seems to be the only useful option. Of course, you may use CSV to rowset convertion using double-SUBSTRING_INDEX with generated or static numbers table too... – Akina Jun 07 '21 at 09:44
  • This is by no means a simple task for sql. Sql is meant to handle data in a structured database, not an arbitrary dataset you provide in any random format through copy-pasting. Some database client applications can turn data pasted in their gui into create table statements (most web-based sql fiddles have this feature), but the functionality is provided by the client application, not by sql. JSON_TABLE() would be the closes thing to what you are trying to do in mysql, but that's only available in v8.0 and would require your data be in json format. Doing this in sql is not really worth it atm. – Shadow Jun 07 '21 at 10:12
  • Fix your data model! Don't store multiple values in a string. Period. – Gordon Linoff Jun 07 '21 at 11:24
  • @GordonLinoff I agree with your general sentiment here and I also understand that mysql is not supposed to be used this way but in some rare cases data is coming from logs and you need to invoke some functions before that data/ids are useable in queries etc https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html https://dev.mysql.com/doc/refman/8.0/en/string-functions.html – M-Wajeeh Jun 07 '21 at 12:01
  • @GordonLinoff those multiple values are not stored in a string in database. Those value are coming from external sources so I want to sanitize them before using them in queries. – M-Wajeeh Jun 07 '21 at 12:06
  • @M-WaJeEh . . . When you import the data, you should use the ETL to transform the data into the appropriate data forms. – Gordon Linoff Jun 07 '21 at 12:28

1 Answers1

1

version: 5.7.30, max values are like 1000. – M-WaJeEh

Possible solution.

  1. Create service table which stores integers from 1 to 1000. You may create it in your database, in some service database, or even in mysql database.

  2. Use the next code (a pattern from the question used):

SELECT myCol*10
FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@arg, ',', numbers.num), ',', -1) as myCol 
       FROM mysql.numbers 
       WHERE numbers.num <= LENGTH(@arg) - LENGTH(REPLACE(@arg, ',', '')) + 1 ) source;

where @arg is placeholder for CSV string ('800, 776, 7803, 827, 91, 976, 908').

Of course, you may provide the amount of values in CSV into WHERE expression instead of commas counting.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0042c8c9e5ffe46eee7a57e628ef75f8

Akina
  • 39,301
  • 5
  • 14
  • 25