CODEWITHSUNDEEP
mysqlsqlfunctionpostgresqlgenerate-series
Sfisioza
Sfisioza

Reputation: 3940

generate_series in MySQL

What is the PostgreSQL's generate_series() equivalent in MySQL?

How to convert this query to MySQL?

select substr('some-string', generate_series(1, char_length('some-string')))

Sample output from PostgreSQL:

some-string
ome-string
me-string
e-string
-string
string
tring
ring
ing
ng
g

select generate_series(1, char_length('some-string'))

1
2
3
4
5
6
7
8
9
10
11

Final solution:

CREATE TABLE `numberlist` (
 `id` tinyint(4) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
)

INSERT INTO `numberlist` values(null)
(repeat the above query the maximum string you need)

SELECT substr('somestring', id) 
FROM numberlist 
WHERE id <= character_length('somestring')

Upvotes: 2

Views: 5251

Answers (1)

Sparky
Sparky

Reputation: 15125

Here is the concept, but I don't have mySQL installed on this box. You will need to create a table of integers, using AUTO INCREMENT. A table of numbers is generally a handy table to have available in a database, and would only need be created once

create table NumberList (id MEDIUMINT NOT NULL AUTO_INCREMENT,fill char(1))

declare @x INT
set @x=0
while @x < 20
begin
    insert into numberList values(null)
    Set @x = @x+1
end

Then, join this table as shown below using the LIMIT clause

select substr('somestring',id) 
from numberlist
limit len('somestring')

I wrote this in SQL server, but it shouldn't be too difficult to convert to mySQL...

The code below SHOULD work in mySQL

DECLARE xx INT DEFAULT 0;
  WHILE xx < 20 DO
    insert into numberList values(null)        
    SET xx = xx + 1;
 END WHILE;

Upvotes: 3

Related Questions