incrementing alpha string using SQL

Question

incrementing alpha string using SQL

familysupportdba

Old Hand

Points: 364
More actions
November 16, 2005 at 8:21 pm

#364173

I am working with EVENT_LOCATOR varchar(40) value. An example EVENT_LOCATOR would be "2005-AAAHNY". The "2005-" first 5 characters of the string would never be affected by incrementing the string; the last 6 characters would be. Acceptable values for the last 6 characters are uppercase A through uppercase Z. So, for example:
"2005-AAAHNY" incremented once would become "2005-AAAHNZ". Since Z is the highest you can go for each alpha character, "2005-AAAHNZ" incremented one would become "2005-AAAHOA" Further incrementation would produce
2005-AAAHOB
2005-AAAHOC
2005-AAAHOD
etc.
What I would like to be able to do is to increment the string using SQL. A UDF, for example, that when given "2005-AAAHNZ", would return "2005-AAAHOA".
Any ideas?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jan-155192 SSCrazy Points: 2613 More actions · Answer 1

This would be a solution to your problem. (Probably some extra error handling will be needed, to check that it only accepts upper case chars, and so on..) But the recursive char incrementation should work fine.

Regards,

Jan

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'fnIncrementString')

DROP FUNCTION fnIncrementString

GO

CREATE FUNCTION fnIncrementString(@sChar varchar(6))

RETURNS varchar(6)

AS

BEGIN

DECLARE @nLastChar smallint

DECLARE @sReturn varchar(6)

SELECT @nLastChar = ASCII(SUBSTRING(@sChar, LEN(@sChar),1))

IF @nLastChar = 90

SELECT @sReturn = dbo.fnIncrementString(SUBSTRING(@sChar, 1, LEN(@sChar) - 1)) + 'A'

ELSE

SELECT @sReturn = SUBSTRING(@sChar, 1, LEN(@sChar) - 1) + CHAR(@nLastChar + 1)

RETURN @sReturn

END

GO

-- =============================================

-- Example to execute function

-- =============================================

SELECT dbo.fnIncrementString('AAAHOC')

SELECT dbo.fnIncrementString('AAAHOZ')

SELECT dbo.fnIncrementString('AZZZZZ')

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736212

November 17, 2005 at 7:43 am

#604415

This is a great place for SQLCLR in SQL Server 2005.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

Great work Jan,

I had some issues with the T-SQL so changed the function a little:

CREATE FUNCTION fn_IncString (@String varchar(6))

RETURNS varchar(6)

AS

BEGIN

DECLARE @C smallint,@Return varchar(6)

SELECT @C=ASCII(RIGHT(@String,1))

IF @C=90

SELECT @Return=dbo.fn_IncString(LEFT(@String,LEN(@String)-1))+'A'

ELSE

SELECT @Return=LEFT(@String,LEN(@String)-1)+CHAR(@C+1)

RETURN @Return

END

Andy

familysupportdba Old Hand Points: 364 More actions · Answer 4

Thanks, guys!. I didn't know you could do recursion within a function in TSQL.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Brilliant use of recursion guys! Absolutely awesome.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K2mission SSC-Addicted Points: 461 More actions · Answer 6

Great job, Jan-155192. I'm going to modify to include numerics; ie. 'AAAAAZ' will increment to 'AAAAA0' and 'AAAAA9' would increment to 'AAAABA'. I'll update the post after I'm done for anyone that may also need.

Thank you

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

My last response on this was nearly a decade ago. I no longer believe that recursion of a scalar function would be the right way to go nor do I believe that incrementing alpha values are, either. You can spell a huge number of highly offensive words in just 4 letters, never mind six.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Talada SSChampion Points: 11954 More actions · Answer 8

K2mission (2/2/2015)
Great job, Jan-155192. I'm going to modify to include numerics; ie. 'AAAAAZ' will increment to 'AAAAA0' and 'AAAAA9' would increment to 'AAAABA'. I'll update the post after I'm done for anyone that may also need.
Thank you

Hmm, you're using sort of a mixed up Base 36 which would go 0-9 then A-Z.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

K2mission (2/2/2015)
Great job, Jan-155192. I'm going to modify to include numerics; ie. 'AAAAAZ' will increment to 'AAAAA0' and 'AAAAA9' would increment to 'AAAABA'. I'll update the post after I'm done for anyone that may also need.
Thank you

I have never figured out a situation where this type of "incrementing" makes any sense. What is the point? Are you going to sort these values? Is there some reason you need to use such a strange value? What is wrong with an incrementing number value?

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

Jeff Moden (2/2/2015)
My last response on this was nearly a decade ago. I no longer believe that recursion of a scalar function would be the right way to go nor do I believe that incrementing alpha values are, either. You can spell a huge number of highly offensive words in just 4 letters, never mind six.

No fair. I only know two of them.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

ChrisM@home (2/2/2015)
Jeff Moden (2/2/2015)
My last response on this was nearly a decade ago. I no longer believe that recursion of a scalar function would be the right way to go nor do I believe that incrementing alpha values are, either. You can spell a huge number of highly offensive words in just 4 letters, never mind six.
No fair. I only know two of them.

Work and Debt?

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Bill Talada SSChampion Points: 11954 More actions · Answer 12

The only place I've used Base36 is to jam a lot of numbers into a short barcode. Just think how long a binary number is compared to a hex number.

And the second reason to use Base36 is for the four-letter words you can make!

K2mission SSC-Addicted Points: 461 More actions · Answer 13

Sean,

The thought was to allow for as many 6-character combinations as possible. Using the alphabet along with numbers offered 2,176,782,336 combinations whereas numbers alone would only offer 1,000,000 (zero-base). However, in light of the many spellings that could be fabricated from this arrangement (thanks to all for the feedback and laughs), it's best that other solutions are utilized. Our team had restrictions in what was permitted to be sent to a vendor. We were able to work out another format that better represented the needs of each party.

Thanks,

Steve

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

K2mission (2/9/2015)
Sean,
The thought was to allow for as many 6-character combinations as possible. Using the alphabet along with numbers offered 2,176,782,336 combinations whereas numbers alone would only offer 1,000,000 (zero-base). However, in light of the many spellings that could be fabricated from this arrangement (thanks to all for the feedback and laughs), it's best that other solutions are utilized. Our team had restrictions in what was permitted to be sent to a vendor. We were able to work out another format that better represented the needs of each party.
Thanks,
Steve

At least remove the vowels and the letter "Y" from the mix so that you don't spell out so many offensive words in the process.

To be honest though, it only takes 4 bytes to get numbers from 0 to over two billion. Have a display limit of six bytes is a pretty silly requirement you've been given computationally speaking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)