generate_series in MySQL

Question

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')

Can you show an example of the output of above in PostgreSQL? I think I know and have a way it should work in mySQL, but want to see your expected output before suggesting an answer. Thanks — Sparky
– Sparky, Commented Sep 20, 2011 at 20:51
Do series generate in reverse like that? I would have thought it would start with g, then ng, then ing, etc... — Sparky
– Sparky, Commented Sep 20, 2011 at 21:00
@Sparky, copied the exact output. But I think order does not matter in my case, I just need the patterns. — takeshin
– takeshin, Commented Sep 20, 2011 at 21:05
generate_series just generates integers in order. You're seeing substr's behavior. — Daniel Lyons
– Daniel Lyons, Commented Sep 20, 2011 at 21:05

Sparky · Accepted Answer · 2011-09-20 23:11:13Z

3

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;

edited Sep 20, 2011 at 23:11

answered Sep 20, 2011 at 21:27

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Daniel Lyons Over a year ago

MySQL doesn't have DECLARE, SET or WHILE.

Bohemian Over a year ago

@Daniel mysql does have set eg set somevar := 0;

Sparky Over a year ago

Thanks guys, I've been working in MS SQL so much lately, but hope the SQL is close enough it can be easily converted...

Daniel Lyons Over a year ago

Does it have WHILE somewhere I haven't seen? If not, this isn't going to work.

Sfisioza Over a year ago

@Sparky This declare does not work on MySQL. But I inserted the data manually. The next thing is that the correct function name to determine length is: char_length, but this does not work in limit. Any suggestions?

|

Collectives™ on Stack Overflow

generate_series in MySQL

Final solution:

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Final solution:

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related