SQLTeam.com | Weblogs | Forums

Adding one to the value if the Column is a varchar and mixed bag of Letter or numbers

If i have a set of values which is nvarchar(12) and i have to take the value and add one to it.
What is the best approach to code this.

0009 becomes 0010
0010 becomes 0011

ADMN01 becomes ADMN02
ARA becomes ARA0000
ARA000 becomes ARA001

If no value are a Letter i.e ARA then default to four 0000

my recommendation would be dont do it
0009 is not an numeric value but you want to add +1 to it.

What happens to future numbers?
what happens to currently existing "number" what if there already is a 0010?

etc

I found this Check the first occurence of a numeric in a string (microsoft.com)

Used it - and it was close enough to get by should i get over a million entries to update.

Do you want to SELECT the value or UPDATE to the new value?

Just the new value next to it. So i can see the before and after values.


;WITH test_data AS (
    SELECT CAST(N'ADMN01' AS nvarchar(12)) AS value UNION ALL
    SELECT 'ARA' UNION ALL
    SELECT 'ARA000'
)
SELECT 
    td.value,
    LEFT(td.value, first_digit - 1) + RIGHT(REPLICATE('0', LEN(digits) - 1) + 
        CAST(calc2.digits + 1 AS varchar(30)), LEN(digits)) AS new_value
FROM test_data td
CROSS APPLY (
    SELECT CASE WHEN PATINDEX('%[0-9]%', value) = 0 THEN LEN(value) + 1 
        ELSE PATINDEX('%[0-9]%', value) END AS first_digit
) AS calc1
CROSS APPLY (
    SELECT CASE WHEN PATINDEX('%[0-9]%', value) = 0 THEN '0000' 
        ELSE SUBSTRING(value, first_digit, 10) END AS digits
) AS calc2
1 Like

Wow - that is fantastic - Thank you

You're very welcome.