Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding alphabet to a number to make it unique Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 3:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:42 AM
Points: 2, Visits: 6
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)

Insert into #temp_Alpha_num(uniquenum,Somenum)
values
( 1 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
( 2 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 3 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 4 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 5 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 6 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 7 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values( 8 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 9 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 10 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 11 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 12 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 13 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 14 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 15 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 16 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 17 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 18 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 19 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 20 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 21 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 22 , 121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 23 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 24 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 25 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 26 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 27 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 28 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 29 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 30 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 31 , 101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values ( 32 , 101)

for the somenum column I need to add alphabets to make them unique. for example 121a,121b,121c....121z,121aa,121ab,101a and so on...


Could you guys please help. I dont know where to start..
Post #1517487
Posted Monday, November 25, 2013 5:19 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:01 PM
Points: 735, Visits: 4,703
You mean something like this?:

SELECT UniqueNum
,Somenum
, CHAR(ROW_NUMBER() OVER (PARTITION BY SomeNum ORDER BY UniqueNum ASC)+64) AS LetterSuffix
FROM #temp_Alpha_num
ORDER BY uniquenum,Somenum ASC;

so that when you get 121A to 121F and then maybe 101A to 101X or similar?
Post #1517497
Posted Monday, November 25, 2013 5:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
What happens when you get to 4 letter combinations?

Could get nasty!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1517503
Posted Tuesday, November 26, 2013 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:42 AM
Points: 2, Visits: 6
once the somenum exceeds the alphabet range it should be aa ab ac ad .but this code gives me other characters ([,],$,^,(,),)..
Post #1517715
Posted Tuesday, November 26, 2013 9:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 13,282, Visits: 12,116
gavva.sravan (11/26/2013)
once the somenum exceeds the alphabet range it should be aa ab ac ad .but this code gives me other characters ([,],$,^,(,),)..


That is because this is a bad idea. Once you start "auto incrementing" characters you open up a whole possibility of bad things. What happens when you start getting into 3 and 4 letter words?

You already have a unique value per row. Why can't you use that? Or if you really need to use somenum why not cast it to a varchar add an underscore and then use row_number?

Something like this maybe?

select uniquenum, Somenum, CAST(Somenum varchar(10)) + '_' + cast(Suffix as varchar(5)) as SomeNum_PlusRowNum
from
(
SELECT UniqueNum
,Somenum
, ROW_NUMBER() OVER (PARTITION BY SomeNum ORDER BY UniqueNum ASC) AS Suffix
FROM #temp_Alpha_num
)
ORDER BY uniquenum, Somenum ASC;



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1517725
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse