Home » SQL & PL/SQL » SQL & PL/SQL » sequential string generation
sequential string generation [message #297796] Sun, 03 February 2008 14:26 Go to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Anybody know of a sql function or plsql function or database package or even java I can load into the database that generates sequential strings? For example:

select rownum from dba_objects

gives

    ROWNUM
----------
         1
         2
...
         9
        10
        11
...

I would like some similar function that gives sequential strings. Assuming we keep it simple, just A-Z uppercase, then I am "fingers crossed" looking for something like this:

select sequential_string from dba_objects

to give this kind of result

SEQUENTIAL_STRING
-----------------
A
B
...
Y
Z
AA
AB
...
AY
AZ
BA
...


Anyone come across this kind of functionality in their travels or am I stuck with inventing something for myself?

Kevin
Re: sequential string generation [message #297802 is a reply to message #297796] Sun, 03 February 2008 18:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION sequential_string
  2    (p_num IN NUMBER)
  3    RETURN	 VARCHAR2
  4  AS
  5    v_num	 NUMBER := p_num - 1;
  6    v_string  VARCHAR2 (32767);
  7  BEGIN
  8    LOOP
  9  	 v_string := CHR (65 + MOD (v_num, 26)) || v_string;
 10  	 v_num := TRUNC (v_num / 26);
 11  	 EXIT WHEN v_num = 0;
 12  	 v_num := v_num - 1;
 13    END LOOP;
 14    RETURN v_string;
 15  END sequential_string;
 16  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT sequential_string (ROWNUM) FROM dba_objects WHERE ROWNUM <= 100
  2  /

SEQUENTIAL_STRING(ROWNUM)
--------------------------------------------------------------------------------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
AR
AS
AT
AU
AV
AW
AX
AY
AZ
BA
BB
BC
BD
BE
BF
BG
BH
BI
BJ
BK
BL
BM
BN
BO
BP
BQ
BR
BS
BT
BU
BV
BW
BX
BY
BZ
CA
CB
CC
CD
CE
CF
CG
CH
CI
CJ
CK
CL
CM
CN
CO
CP
CQ
CR
CS
CT
CU
CV

100 rows selected.

SCOTT@orcl_11g> 

Re: sequential string generation [message #297803 is a reply to message #297796] Sun, 03 February 2008 18:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
-- or using a sequence:
SCOTT@orcl_11g> CREATE SEQUENCE seq_for_string
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION sequential_string
  2    RETURN	 VARCHAR2
  3  AS
  4    v_num	 NUMBER;
  5    v_string  VARCHAR2 (32767);
  6  BEGIN
  7    SELECT seq_for_string.NEXTVAL - 1 INTO v_num FROM DUAL;
  8    LOOP
  9  	 v_string := CHR (65 + MOD (v_num, 26)) || v_string;
 10  	 v_num := TRUNC (v_num / 26);
 11  	 EXIT WHEN v_num = 0;
 12  	 v_num := v_num - 1;
 13    END LOOP;
 14    RETURN v_string;
 15  END sequential_string;
 16  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT sequential_string FROM dba_objects WHERE ROWNUM <= 100
  2  /

SEQUENTIAL_STRING
--------------------------------------------------------------------------------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
AR
AS
AT
AU
AV
AW
AX
AY
AZ
BA
BB
BC
BD
BE
BF
BG
BH
BI
BJ
BK
BL
BM
BN
BO
BP
BQ
BR
BS
BT
BU
BV
BW
BX
BY
BZ
CA
CB
CC
CD
CE
CF
CG
CH
CI
CJ
CK
CL
CM
CN
CO
CP
CQ
CR
CS
CT
CU
CV

100 rows selected.

SCOTT@orcl_11g> 



Re: sequential string generation [message #297804 is a reply to message #297796] Sun, 03 February 2008 18:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member

-- or passing the sequence into the function instead of selecting from it within the function:

SCOTT@orcl_11g> CREATE SEQUENCE seq_for_string
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION sequential_string
  2    (p_num IN NUMBER)
  3    RETURN	 VARCHAR2
  4  AS
  5    v_num	 NUMBER := p_num - 1;
  6    v_string  VARCHAR2 (32767);
  7  BEGIN
  8    LOOP
  9  	 v_string := CHR (65 + MOD (v_num, 26)) || v_string;
 10  	 v_num := TRUNC (v_num / 26);
 11  	 EXIT WHEN v_num = 0;
 12  	 v_num := v_num - 1;
 13    END LOOP;
 14    RETURN v_string;
 15  END sequential_string;
 16  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT sequential_string (seq_for_string.NEXTVAL) FROM dba_objects WHERE ROWNUM <= 100
  2  /

SEQUENTIAL_STRING(SEQ_FOR_STRING.NEXTVAL)
--------------------------------------------------------------------------------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
AR
AS
AT
AU
AV
AW
AX
AY
AZ
BA
BB
BC
BD
BE
BF
BG
BH
BI
BJ
BK
BL
BM
BN
BO
BP
BQ
BR
BS
BT
BU
BV
BW
BX
BY
BZ
CA
CB
CC
CD
CE
CF
CG
CH
CI
CJ
CK
CL
CM
CN
CO
CP
CQ
CR
CS
CT
CU
CV

100 rows selected.

SCOTT@orcl_11g> 

Re: sequential string generation [message #297812 is a reply to message #297796] Sun, 03 February 2008 21:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, I get it, you guys have shown me it is a base conversion problem. Convert from base10 to base26.

Thanks everyone, this was just what I was looking for.

Kevin
Re: sequential string generation [message #297909 is a reply to message #297812] Mon, 04 February 2008 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a little more complicated than just a conversion from base 10 to base 26 as the character set for each position has 26 or 27 values (including null) and not the same number in a real arithmetic base (0-9 or A-Z).

Regards
Michel
Re: sequential string generation [message #297918 is a reply to message #297796] Mon, 04 February 2008 04:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, but in the end, I don't need a true base conversion, I wanted something that basically translates rownum to letters A-Z. I can expand on it from here if I need to. I should have been able to write this code myself now that I see it. Oh well, some days we are denser than others I guess.

Kevin
Re: sequential string generation [message #298170 is a reply to message #297918] Tue, 05 February 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If someone is interested in a SQL solution, here's one valid up to 12356629 ('ZZZZZ') but it is easy to extend it for greater number.
SQL> with data as (select level-1 rn from dual connect by level < 30)
  2  select rn,
  3         case
  4           when rn < (0+1)*26 /* 26 */
  5             then chr(ascii('A')+mod(rn,26))
  6           when rn < (26+1)*26 /* 702 */ 
  7             then chr(ascii('A')+trunc((rn-26)/26)) 
  8                  || chr(ascii('A')+mod(rn,26))
  9           when rn < (702+1)*26 /* 18278 */
 10             then chr(ascii('A')+trunc((rn-702)/26/26))
 11                  || chr(ascii('A')+mod(trunc((rn-702)/26),26)) 
 12                  || chr(ascii('A')+mod(rn,26))
 13           when rn < (18278+1)*26 /* 475254 */
 14             then chr(ascii('A')+trunc((rn-18278)/26/26/26))
 15                  || chr(ascii('A')+mod(trunc((rn-18278)/26/26),26))
 16                  || chr(ascii('A')+mod(trunc((rn-18278)/26),26)) 
 17                  || chr(ascii('A')+mod(rn,26))
 18           when rn < (475254+1)*26 /* 12356630 */
 19             then chr(ascii('A')+trunc((rn-475254)/26/26/26/26))
 20                  || chr(ascii('A')+mod(trunc((rn-475254)/26/26/26),26))
 21                  || chr(ascii('A')+mod(trunc((rn-475254)/26/26),26))
 22                  || chr(ascii('A')+mod(trunc((rn-475254)/26),26)) 
 23                  || chr(ascii('A')+mod(rn,26))
 24           else '#####'
 25        end seq
 26  from data
 27  /
        RN SEQ
---------- -----
         0 A
         1 B
         2 C
         3 D
         4 E
         5 F
         6 G
         7 H
         8 I
         9 J
        10 K
        11 L
        12 M
        13 N
        14 O
        15 P
        16 Q
        17 R
        18 S
        19 T
        20 U
        21 V
        22 W
        23 X
        24 Y
        25 Z
        26 AA
        27 AB
        28 AC

29 rows selected.

Regards
Michel
Re: sequential string generation [message #299415 is a reply to message #297796] Mon, 11 February 2008 23:32 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
SQL> set pagesize 10000
SQL> 
SQL> with t as (select ' ABCDEFGHIJKLMNOPQRSTUVWXYZ' str from dual),
  2      t1 as (select str, level lvl from t connect by level<=1000)
  3  --
  4      select num from (select part, num, str from t1
  5       model
  6        partition by (lvl part)
  7        dimension by (0 dim)
  8        measures (lvl, cast(null as varchar2(1000)) num, str)
  9         rules iterate (1e6) until (lvl[0] <= 0)
 10          (num[0] = substr(str[0],mod(lvl[0],length(str[0]))+1,1)||num[0],
 11           lvl[0] = trunc(lvl[0]/length(str[0])))
 12           )
 13        where instr(num, ' ')=0
 14        order by part
 15  /

NUM
--------------------------------------------------------------------------------
A
B
C
D
E
F
G
H
...
X
Y
Z
AA
AB
AC
AD
AE
...
AX
AY
AZ
BA
BB
BC
BD
BE
...
BW
BX
BY
BZ
CA
CB
CC
CD
...
YW
YX
YY
YZ
ZA
ZB
ZC
ZD
...
ZW
ZX
ZY
ZZ
AAA
AAB
AAC
AAD
AAE
AAF
AAG
AAH
...
AIS
AIT
AIU
AIV
AIW
AIX
AIY
AIZ
AJA

937 rows selected

SQL> 


Probably, there is an algorithm do reduce the extra data scan on the outer query (where instr(str,' ')=0), but I have not come up with it.

The logic of the similar query is explained here: http://volder-notes.blogspot.com/2008/01/combinatorial-problem.html
Re: sequential string generation [message #299578 is a reply to message #299415] Tue, 12 February 2008 08:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I'd just like to mention Volder, that I appreciate the link to your site. I have added a bookmark for it under my reference links.
Re: sequential string generation [message #299628 is a reply to message #299415] Tue, 12 February 2008 14:56 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
As both my model solutions suck in perfomance compared to a solution from Michel, don't take it serious.
The first model was kind of radix change, the following is just iterative string manipulation with model:
SQL> set pagesize 1000
SQL> 
SQL> select * from dual
  2   model
  3    dimension by (0 dim)
  4    measures(cast('A' as varchar2(1000)) num)
  5     rules
  6      iterate(100)
  7       (num[iteration_number+1] = case when ltrim(num[CV()-1],'Z') is null
  8                                       then rpad('A',length(num[CV()-1])+1,'A')
  9                                       else rpad(regexp_replace(num[CV()-1],'^(.*)[^Z]Z*$','\1')||
 10                                                 chr(ascii(substr(rtrim(num[CV()-1],'Z'),-1,1))+1),
 11                                                 length(num[CV()-1]),'A')
 12                                  end
 13       )
 14  /

       DIM NUM
---------- --------------------------------------------------------------------------------
         0 A
         1 B
         2 C
         3 D
         4 E
         5 F
         6 G
         7 H
         8 I
        ...
        23 X
        24 Y
        25 Z
        26 AA
        27 AB
        28 AC
        29 AD
        30 AE
        31 AF
        32 AG
        33 AH
        34 AI
        ...
        97 CT
        98 CU
        99 CV
       100 CW

101 rows selected

SQL> 
Re: sequential string generation [message #301022 is a reply to message #297918] Tue, 19 February 2008 00:33 Go to previous message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Here's another simple and more easily extensible solution vaild up to 12356630 = 26 / 25 * (POWER (26, 5) - 1) rows.


SELECT (CASE WHEN LEVEL > 26 / 25 * (POWER (26, 4) - 1) 
             THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (POWER (26, 4) - 1)) / POWER (26, 4)) - 1, 26))
        END)
    || (CASE WHEN LEVEL > 26 / 25 * (POWER (26, 3) - 1) 
             THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (POWER (26, 3) - 1)) / POWER (26, 3)) - 1, 26))
        END)
    || (CASE WHEN LEVEL > 26 / 25 * (POWER (26, 2) - 1) 
             THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (POWER (26, 2) - 1)) / POWER (26, 2)) - 1, 26))
        END)
    || (CASE WHEN LEVEL > 26 / 25 * (POWER (26, 1) - 1) 
             THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (POWER (26, 1) - 1)) / POWER (26, 1)) - 1, 26))
        END)  
    || (CASE WHEN LEVEL > 26 / 25 * (POWER (26, 0) - 1) 
             THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (POWER (26, 0) - 1)) / POWER (26, 0)) - 1, 26))
        END) SEQ
  FROM DUAL
CONNECT BY LEVEL <= 26 / 25 * (POWER (26, 5) - 1)



or this:


SELECT (CASE WHEN LEVEL > 26 / 25 * (P4 - 1) THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (P4 - 1)) / P4) - 1, 26)) END)
    || (CASE WHEN LEVEL > 26 / 25 * (P3 - 1) THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (P3 - 1)) / P3) - 1, 26)) END)
    || (CASE WHEN LEVEL > 26 / 25 * (P2 - 1) THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (P2 - 1)) / P2) - 1, 26)) END)
    || (CASE WHEN LEVEL > 26 / 25 * (P1 - 1) THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (P1 - 1)) / P1) - 1, 26)) END)  
    || (CASE WHEN LEVEL > 26 / 25 * (P0 - 1) THEN CHR (65 + MOD (CEIL ((LEVEL - 26 / 25 * (P0 - 1)) / P0) - 1, 26)) END) SEQ
  FROM (SELECT POWER (26, 0) P0, POWER (26, 1) P1, POWER (26, 2) P2, POWER (26, 3) P3, POWER (26, 4) P4, POWER (26, 5) P5 FROM DUAL)
CONNECT BY LEVEL <= 26 / 25 * P5 - 1



Query Your Dream & Future at
http://www.soqool.com

[Updated on: Tue, 19 February 2008 01:44]

Report message to a moderator

Previous Topic: selecting the column in subquery based on date
Next Topic: CHECK Constraint
Goto Forum:
  


Current Time: Sun Dec 04 02:52:12 CST 2016

Total time taken to generate the page: 0.24996 seconds