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

Incremental pattern matching Expand / Collapse
Author
Message
Posted Friday, July 30, 2010 6:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
Hi all,

I've got a column, called user_code char(3) that is used to store unique codes. The patterns are as follows

Character 1 is A-Z
Character 2 is 0-9,A-Z
Character 3 is 1-9,0,A-Z

So, the data starts at

A01
A02
...
AZZ
B01
B02
..
BZZ
..
ZZZ

Now, what I need to do is write a T-SQL statement that will tell me what the next available (ie unused) user_code can be. I wish I could just do a MAX(user_code) to find the most recently used one and then work out the next value in the sequence, but it's not that simple, as the user_codes assigned aren't in any meaningful order..

If you could help me with this, that'd be awesome. There are no constraints here really, if it means creating another lookup table with all the options in, then that's allowed, as long as the solution works!

Post #961287
Posted Friday, July 30, 2010 6:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic961290-338-1.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #961309
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse