Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Incremental pattern matching Expand / Collapse
Posted Friday, July 30, 2010 6:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 8:17 AM
Points: 84, Visits: 594
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


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



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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