User Defined Function

Question

User Defined Function

vs.satheesh

SSCrazy

Points: 2392
More actions
October 19, 2012 at 10:58 am

#283178

Hi All
In My project one table my old employees using following format id generation.Hoe to generate this sequence using stored procedure or user defined function?
AAA
AAB
AAC
...
...
...
ABA
ABB
ABC
...
...
...
BAA
BAB
Please help me

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

vs.satheesh (10/19/2012)
Hi All
In My project one table my old employees using following format id generation.Hoe to generate this sequence using stored procedure or user defined function?
AAA
AAB
AAC
...
...
...
ABA
ABB
ABC
...
...
...
BAA
BAB
Please help me

If at all possible do something different. This is painful to work with. What happens when a row gets deleted? Do you just have a hole or do you have to update everything to fill in the hole?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

besides Seans sage advice, noone on your side has thought through possible max values, right?

if you force a min length of 3 characters, and also a max length of three chracters , AAA to ZZZ, no A ro ZZ allowed)

the most values you could have is 16874 total rows.

i've see this request a lot, and it's alwasy just a bad idea, where someone wants a displayable semi random unique display that they don't really need in the firstplace.

is there a problem with me being customerId = 18422 in your database?

That's how most of the world does it...

select (26 * 26 * 26) --max 3 number combinations

- ((26 * 26) --eliminate two character

+ 26) --eliminate character options

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vs.satheesh SSCrazy Points: 2392 More actions · Answer 3

Thank U for reply

My requirement like that.

26*26*26 is enough for me.please help me

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

I have to agree that this is a bad idea. Limited and hard to work with, but this will generate the combinations.

create table #x (letter char(1) not null)

insert #x select 'a'

insert #x select 'b'

insert #x select 'c'

insert #x select 'd'

insert #x select 'e'

insert #x select 'f'

insert #x select 'g'

insert #x select 'h'

insert #x select 'i'

insert #x select 'j'

insert #x select 'k'

insert #x select 'l'

insert #x select 'm'

insert #x select 'n'

insert #x select 'o'

insert #x select 'p'

insert #x select 'q'

insert #x select 'r'

insert #x select 's'

insert #x select 't'

insert #x select 'u'

insert #x select 'v'

insert #x select 'w'

insert #x select 'x'

insert #x select 'y'

insert #x select 'z'

select a.letter + b.letter + c.letter from

#x a cross join #x b

cross join #x c

And then again, I might be wrong ...
David Webb

Lowell SSC Guru Points: 323518 More actions · Answer 5

--three char table: bigger range

CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 17577)),

XCALCULATED AS

CHAR(((XID -1)/ (26*26))%(26) + 65)

+ CHAR(((XID -1)/ 26)%(26) + 65)

+ CHAR(((XID -1)%(26)) + 65)

, --The 4 digit numeric part

SOMEOTHERCOL VARCHAR(30)

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Here is another way to do it if you have a Tally table. You could insert the output of this into a permanent table and assign a RowNum using ROW_NUMBER or Identity. Then you could retrieve the "next" one when you need it.

;with Chars as

(

select *

from Tally

where N > 64 and N <= 90

)

select char(c1.N) + char(c2.N) + char(c3.N) as SomeKey

from Chars c1, Chars c2, Chars c3

order by char(c1.N) + char(c2.N) + char(c3.N)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/