首页 文章

SQL,它在给定的起点和终点之间生成字母序列

提问于
浏览 1358
2

我需要一个SQL,它在给定的起点和终点之间生成字母序列 .

喜欢,对于 Start='C' End='G' 输出应该是

C
D
E
F
G

3 回答

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

    像这样使用 connect by (没有 start with 和只取决于级别的结束条件)是未记录的(并且不受支持),因此它可能会中断任何时间(尽管我不知道任何不适用的版本) .

    从11.2开始,您还可以使用递归公用表表达式:

    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;
    
  • 0

    这样的事情起作用了

    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)
    
  • 0

    修改了@a_horse_with_no_name,如下所示

    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
    

相关问题