Alphanumeric three digits sequential number generation in SQL


CREATE TABLE #l (letter char(1))

INSERT

INTO #l

SELECT

‘a’

UNION ALL

SELECT ‘b’

UNION ALL

SELECT ‘c’

UNION ALL

SELECT ‘d’

UNION ALL

SELECT ‘e’

UNION ALL

SELECT ‘f’

UNION ALL

SELECT ‘g’

UNION ALL

SELECT ‘h’

UNION ALL

SELECT ‘i’

UNION ALL

SELECT ‘j’

UNION ALL

SELECT ‘k’

UNION ALL

SELECT ‘l’

UNION ALL

SELECT ‘m’

UNION ALL

SELECT ‘n’

UNION ALL

SELECT ‘o’

UNION ALL

SELECT ‘p’

UNION ALL

SELECT ‘q’

UNION ALL

SELECT ‘r’

UNION ALL

SELECT ‘s’

UNION ALL

SELECT ‘t’

UNION ALL

SELECT ‘u’

UNION ALL

SELECT ‘v’

UNION ALL

SELECT ‘w’

UNION ALL

SELECT ‘x’

UNION ALL

SELECT ‘y’

UNION ALL

SELECT ‘z’

 

CREATE

TABLE #n (number int)

INSERT

INTO #n

SELECT

0

UNION ALL

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

UNION ALL

SELECT 5

UNION ALL

SELECT 6

UNION ALL

SELECT 7

UNION ALL

SELECT 8

UNION ALL

SELECT 9

 

 

 

 

 

SELECT

id FROM (SELECT id FROM (

SELECT

CAST(a.number as char(1))+ + CAST(b.number as char(1))+ CAST(c.number as char(1)) as id

FROM

#n c CROSS JOIN #n a CROSS JOIN #n b

)

t0

Union ALL

SELECT

id FROM (

SELECT

letter + CAST(a.number as char(1))+ CAST(b.number as char(1)) as id

FROM

#l CROSS JOIN #n a CROSS JOIN #n b

)

t1

Union ALL

SELECT

id FROM (

SELECT

a.letter + b.letter + CAST(number as char(1)) as id

FROM

#l a CROSS JOIN #l b CROSS JOIN #n

)

t2

Union ALL

SELECT

id FROM (

SELECT

a.letter + b.letter + c.letter as id

FROM

#l a CROSS JOIN #l b CROSS JOIN #l c

)

t3

Union ALL

SELECT

id FROM (

SELECT

a.letter + CAST(b.number as char(1)) + c.letter as id

FROM

#l a CROSS JOIN #n b CROSS JOIN #l c

)

t4

)

tt

–WHERE RIGHT(id,2)<>’00’ OR

ORDER

BY id

 

 

–Retrieve the whole set

SELECT

a.id +b.id +c.id as id FROM

(

SELECT letter as id from #l

UNION ALL

SELECT

cast(number as char(1)) FROM #n) a CROSS JOIN

(

SELECT

letter as id from #l

UNION ALL

SELECT

cast(number as char(1)) FROM #n

)

b

CROSS

JOIN

(

SELECT

letter as id from #l

UNION ALL

SELECT

cast(number as char(1)) FROM #n

)

c

–WHERE ISNUMERIC(a.id)=0 OR (ISNUMERIC(a.id)=1 and ISNUMERIC(b.id)=1 AND ISNUMERIC(c.id)=1)

ORDER

BY a.id

DROP

Table #n

DROP

Table #l

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s