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

1

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

2

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

1 Like
3

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.

4

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

5

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

6 

;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
7

Wow - that is fantastic - Thank you

8

You're very welcome.

9

hi

hope this helps

i am trying to do this
why you may ask .. for my own noodles .. for my own practice , for my own stats .. cognition etc etc etc

i put some edge cases in the data

create data script

drop table if exists #Temp
create table #Temp ( Col1 varchar(20))
insert into #Temp select 'ADMN01'
insert into #Temp select 'ADMN1234'
insert into #Temp select 'ADMN1239'
insert into #Temp select 'ARA'
insert into #Temp select 'ARA000'

image

Scotts SQL

SELECT
td.Col1,
LEFT(td.Col1, first_digit - 1) + RIGHT(REPLICATE('0', LEN(digits) - 1) +
CAST(calc2.digits + 1 AS varchar(30)), LEN(digits)) AS new_Col1
FROM #Temp td
CROSS APPLY (
SELECT CASE WHEN PATINDEX('%[0-9]%', Col1) = 0 THEN LEN(Col1) + 1
ELSE PATINDEX('%[0-9]%', Col1) END AS first_digit
) AS calc1
CROSS APPLY (
SELECT CASE WHEN PATINDEX('%[0-9]%', Col1) = 0 THEN '0000'
ELSE SUBSTRING(Col1, first_digit, 10) END AS digits
) AS calc2

using Scotts SQL
image

10

hi

came up with a solution .. hope this helps .. using edge cases

create sample data

drop table if exists #Temp
create table #Temp ( Col1 varchar(20))
insert into #Temp select 'ADMN01'
insert into #Temp select 'ADMN1234'
insert into #Temp select 'ADMN1239'
insert into #Temp select 'ADMN0019'
insert into #Temp select 'ARA'
insert into #Temp select 'ARA000'

select * from #Temp

SELECT  Col1,
        LEFT(Col1, LEN(Col1) - LastCharIndex + 1) + 
        ISNULL(REPLICATE('0', LEN(NumberString) - LEN(CAST(NumberString as int))) + 
        CAST((CAST(NumberString as int) + 1) as varchar(100)),'0000') As Result
FROM #Temp
CROSS APPLY
(
    SELECT PATINDEX('%[^0-9]%', Reverse(Col1)) As LastCharIndex
) As Idx
CROSS APPLY 
(
    SELECT RIGHT(Col1, LastCharIndex - 1) As NumberString
) As NS

image