Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Query to increment an alpha character suffix RE: Query to increment an alpha character suffix

  • February 27, 2018 at 9:39 am

    #1981312

    ScottPletcher - Monday, February 26, 2018 4:54 PM

    I prefer this alternative.  Not for performance reasons, mostly that it allows easier customization of the suffix chars to be assigned.  Sometimes certain chars, such as I and O, are skipped because they look too much like numbers.  Also, the logic below is clearer to me, although that could just be me.

    ;WITH cte_suffix_chars AS (
      SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS suffix_chars
    )
    SELECT A.PONUMBER, A.PODATE,
      A.PONUMBER + SUBSTRING(suffix_chars, ISNULL(CHARINDEX(POSUFFIX, suffix_chars) + rank_num, 1), 1) AS PONUMBERTOUSE
    FROM (
      SELECT *, DENSE_RANK() OVER(PARTITION BY PONUMBER ORDER BY PODATE) AS rank_num
      FROM #PONUMBERTESTIMPORT
    ) AS A
    CROSS JOIN cte_suffix_chars
    LEFT OUTER JOIN (
      SELECT LEFT(PONUMBER, LEN(PONUMBER) - 1) AS PONUMBER, MAX(RIGHT(PONUMBER, 1)) AS POSUFFIX  
      FROM #PONUMBERTEST
      WHERE RIGHT(PONUMBER, 1) NOT LIKE '[0-9]'
      GROUP BY LEFT(PONUMBER, LEN(PONUMBER) - 1)
    ) AS T ON T.PONUMBER = A.PONUMBER

    I considered an approach similar to this, but decided to retain the as much of the original solution as possible while improving the functionality.

    Your group by in your subquery is wrong.  It will produce two groups from the data when it should only produce 1.

    Your query does not appear to handle situations where PONUMBERTEST does not already contain a suffix, i.e., when the table is empty or contains only '12345'.  It produces three rows with '12345A' in both cases, because you replace the null value AFTER adding the dense rank instead of before adding the dense rank.

    I also don't understand why you use a CTE for the constant suffix string instead of declaring a variable.

    I've rewritten your query to correct each of these issues.


    DECLARE @suffixes CHAR(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    SELECT *, i.PONUMBER + SUBSTRING(@suffixes, DENSE_RANK() OVER(PARTITION BY i.PONUMBER ORDER BY i.PODATE) + ISNULL(n.BasePosition,-1), 1)
    FROM #PONUMBERTESTIMPORT i
    OUTER APPLY
    (
        SELECT CHARINDEX(RIGHT(MAX(PONUMBER), 1), @suffixes) AS BasePosition
        FROM #PONUMBERTEST n
        WHERE n.PONUMBER LIKE i.PONUMBER + '%'
    ) n

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA