Create unique char value in Sybase
I need to create a Syb15 stored proc that takes a char(6) @parent and returns a unique char(6) based on @parent. I want to truncate @parent to a max of 3 chars, and then append letters [A-Z] to that value such that the new symbol has not been used before. Here is an example run sequence:
get_sub_symbol( "DISNEY" ) returns "DISA"
get_sub_symbol( "DISNEY" ) returns "DISB" get_sub_symbol( "GE" ) returns "GEA" ... get_sub_symbol( "DISNEY" ) returns "DISZ" get_sub_symbol( "DISNEY" ) returns "DISAA" get_sub_symbol( "DISNEY" ) returns "DISAB" get_sub_symbol( "GE" ) returns "GEB"Performance is important, so if having a new table with (symbol, last_suffix) mappings would be better than processing the lis开发者_如何学JAVAt returned by "select symbol from symbols where symbol like substring(@parent, 1, 3)", then that is ok.
Thanks for your help,
JaredYou answered your own question.
Determine the maximum number of duplicates you'd have. Create a lookup table with that many unique strings. Plus contingency, etc. When lookuping up a new value, take the MAX() of the existing values, look it up, and find the next one.
And yes, storing the shortended name and the suffix separately will help there.
Personally, however, I'd just use an INT as the suffix. No lookup table needed; MAX(suffix) + 1
.
精彩评论