CODEWITHSUNDEEP
sqloracle-database
Kishor Kumar
Kishor Kumar

Reputation: 153

SQL which generates sequence of alphabets between given start and end point

I need a SQL which generates sequence of alphabets between given start and end point.

Like,for Start='C' End='G' output should be

C
D
E
F
G

Upvotes: 2

Views: 943

Answers (3)

Kishor Kumar
Kishor Kumar

Reputation: 153

Modified @a_horse_with_no_name answer as below

WITH X(lettr) AS 
(SELECT 'C' lettr FROM DUAL
 UNION ALL
 SELECT CHR(ASCII(X.lettr)+1) letter
 FROM X 
 WHERE ASCII(X.lettr)<ASCII('G'))
 SELECT * FROM X

Upvotes: 0

Kishor Kumar
Kishor Kumar

Reputation: 153

Something like this worked

WITH X AS
(SELECT 'C' as St, 'G' as En FROM dual)
SELECT CHR(ASCII(X.St)+ROWNUM-1)
FROM X
CONNECT BY ROWNUM<=(ASCII(X.En)-ASCII(X.St)+1)

Upvotes: 0

user330315
user330315

Reputation: 

select chr(ascii('C') + level - 1) 
from dual
connect by ascii('C') + level - 1 <= ascii('G');

Using connect by like this (no start with and an end condition that only depends on the level) is undocumented (and unsupported) so it might break any time (although I'm not aware of any version where this would not work).

Starting with 11.2 you can also use a recursive common table expression:

with letters (letter, inc) as (
   select 'C', 1 as inc
   from dual
   union all
   select chr(ascii('C') + p.inc), p.inc + 1
   from letters p
   where p.inc < 5
)
select letter
from letters;

Upvotes: 6

Related Questions