incrementing alpha string using SQL

  • 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?

  • 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')

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

  • 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

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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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/

  • 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]

  • 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/

  • 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!

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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