Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Adding alphabet to a number to make it unique Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, November 25, 2013 3:43 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, November 27, 2013 7:42 AM Points: 2, Visits: 6
Post #1517487
 Posted Monday, November 25, 2013 5:19 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Yesterday @ 9:39 PM Points: 501, Visits: 3,354
 You mean something like this?:`SELECT UniqueNum,Somenum, CHAR(ROW_NUMBER() OVER (PARTITION BY SomeNum ORDER BY UniqueNum ASC)+64) AS LetterSuffixFROM #temp_Alpha_numORDER 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 Group: General Forum Members Last Login: Today @ 2:41 AM Points: 3,510, Visits: 4,931
 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 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 Group: General Forum Members Last Login: Today @ 12:17 PM Points: 11,564, Visits: 10,678
 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_PlusRowNumfrom( 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

 Permissions