Inserting Incremented Alphabets into a column

Question

Inserting Incremented Alphabets into a column

Hakuna Matata

SSCertifiable

Points: 5577
More actions
April 19, 2011 at 7:18 am

#389317

Hi,
I have a table that is already populated. I need to add a new column & the values in the column should be AA, AB, AC, AD, AE, .................... AZ, BA, BB, BC, BD, ......................,BZ, CA, CB...
How can I achieve this?
Regards,
Nithin

Viewing 12 posts - 1 through 11 (of 11 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

kr.nithin (4/19/2011)
Hi,
I have a table that is already populated. I need to add a new column & the values in the column should be AA, AB, AC, AD, AE, .................... AZ, BA, BB, BC, BD, ......................,BZ, CA, CB...
How can I achieve this?
Regards,
Nithin

Interesting challenge - what's it for?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 2

This is a Vendor table. This already has a primary key. The requirement is Alphabets as the unique key rather than the numeric primary key.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

kr.nithin (4/19/2011)
This is a Vendor table. This already has a primary key. The requirement is Alphabets as the unique key rather than the numeric primary key.

So the numeric "primary key" isn't unique?

Don't get me wrong, this can be done - just asking a few questions.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 4

Yep In spite of the Numeric Primary Key. We need 2 character Vendor Code for each vendor.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

kr.nithin (4/19/2011)
Yep In spite of the Numeric Primary Key. We need 2 character Vendor Code for each vendor.

How many vendors do you currently have? What is the rate at which you acquire new ones?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 6

120 vendors as of now vendors added at the rate of around 2 for a month. Will it be possible to auto-increment if a new vendor is added.

WayneS SSC Guru Points: 95460 More actions · Answer 7

How's this?

DECLARE @MyTable TABLE (VendorCode CHAR(2));

INSERT INTO @MyTable

SELECT 'AA' UNION ALL

SELECT 'AB' UNION ALL

SELECT 'AC';

WITH cteMiniTally AS

(

-- get numbers from 1-26 (for the letters of the alphabet)

SELECT TOP (26)

RN = ROW_NUMBER() OVER (ORDER BY object_id)

FROM master.sys.all_columns

), cteAvailableVendorCodes AS

(

-- get all codes from AA - ZZ

SELECT VendorCode = CHAR(c1.RN+64) + CHAR(c2.RN+64)

FROM cteMiniTally c1

CROSS JOIN cteMiniTally c2

EXCEPT

-- remove codes already used

SELECT VendorCode

FROM @MyTable

)

-- get the next code to use

SELECT TOP (1)

VendorCode

FROM cteAvailableVendorCodes

ORDER BY VendorCode;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

LOL Wayne that was quick!

;WITH VendorCodes AS

(

SELECT [letter] = CHAR(64 + rn)

FROM (SELECT rn = ROW_NUMBER() OVER(ORDER BY [Name]) FROM sys.columns

) d WHERE rn < 27

)

SELECT c1.letter, c2.letter

FROM VendorCodes c1

CROSS JOIN VendorCodes c2

ORDER BY c1.letter, c2.letter

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 9

ChrisM@home (4/19/2011)
LOL Wayne that was quick!

Yep - not that hard. We're thinking along the same lines here.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 10

WOW !!!!! That worked like a charm :-):-):-):-):-):-):-):-):-):-):-)

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 11

Hats off :-):-):-):-):-):-) To coooooooooooooool !!!!!!!!!!!1