How to flatten results from table-valued function in MySQL?

Question

I am using MySQL, and I have a function which will accept a col data from a table row, and return a table-valued function. for example, a function like this:

CREATE FUNCTION [dbo].[wordlongerthan4](
 @input text
) returns @result table(
 pos int,
 word varchar,
)

which will return the word longer than 4char and its position.

i want to do a sql something like below

select t.name,wordlongerthan4(t.content) from sometable as t;

on table

------------------------------------------------------------------
| id       | name        | content                               |
------------------------------------------------------------------
|1         | my1         | i love something and nothing          |
|2         | my2         | It is a sunny day today               |

to get the result:

|name   | pos | word           |
--------------------------------
|my1    |8    |something       |
|my1    |22   |nothing         |
|my2    |9    |sunny           |
|my2    |20   |today           |

How could i write the sql?(the function wordlongerthan4 is already there,i only need the sql!)

Make use of programming language will be better suit to this task. — ajreal, Apr 22 '15 at 04:12

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

What you are talking about doing is:

Looping through each record and extracting the content.
Splitting the content by a delimiter (space, in this case).
Measuring the length of each part
Adding the part to an array of successful results it its length < n (4).
Returning the part, along with the original id and name from the record it originally came from.

Like ajreal said, this is something that would best be done at the application layer, presumably inside of your php/java/whatever.

There is currently no built-in string explode/split function, but this question here might prove useful: Equivalent of explode() to work with strings in mysql.

edited May 23 '17 at 10:26

Community

1
1

answered Apr 24 '15 at 14:00

Jeff Cashion PhD

674
6
8

No , I mean i have done with the function wordlongerthan4 in some way! But the sql sentence is not legal，how could i make it legal? – Sean Chen Apr 30 '15 at 00:51
It should be: SELECT t.name, wordlongerthan4(t.content) FROM sometable t; – Jeff Cashion PhD May 04 '15 at 12:06

score 0 · Answer 2 · answered Jun 14 '19 at 11:46

You can (ab)use the new JSON_TABLE function in MySQL 8 to do this:

set @input = 'i love something and nothing';

SELECT Value, LENGTH(Value) as Length, LOCATE(Value, @delimited) as pos
     FROM
       JSON_TABLE(
         CONCAT('["', REPLACE(@delimited, ' ', '", "'), '"]'),
         "$[*]"
         COLUMNS(
           Value varchar(50) PATH "$"
         )
       ) data
where LENGTH(Value) > 4;

How to flatten results from table-valued function in MySQL?

2 Answers2