Add 1 to a varchar?

Hello, we have some products that typically look like C014556.

If I want to get the next available number, how do I do it? I tried

SELECT TOP 1 Product + 1

but hit "Conversion failed when converting the varchar value 'C014556' to data type int."

hi hope this helps

1st way .. using LEAD .. windowed function

this is quick solution but depends on how your data is ? the SQL may have to change

create data script

create table #Temp ( product varchar(20))
insert into #Temp select 'C014556'
insert into #Temp select 'C014557'
insert into #Temp select 'C014545'

declare @Product varchar(20) = 'C014556'

;with cte as ( select product , lead(product) over(order by product) as leadProduct from #Temp )
    select 
	     leadProduct 
    from 
	   cte 
	where 
	   product = @product

image

1 Like

hi hope this helps

2nd way .. using translate

this is quick solution but depends on how your data is ? the SQL may have to change

declare @Product varchar(20) = 'C014556'

select  
    product 
from 
    #Temp where cast(TRANSLATE(product, 'C', '0') as int) = cast(TRANSLATE(@product, 'C', '0') as int)+1

image

1 Like

hi hope this helps

3rd way .. using row number and join

this is quick solution but depends on how your data is ? the SQL may have to change

declare @Product varchar(20) = 'C014556'

; with cte as ( select ROW_NUMBER() over(order by product) as rn , * from #Temp ) 
    select 
	     b.product 
    from 
	   cte a 
	      join 
	  cte b 
	     on a.rn + 1 = b.rn and a.product = @Product

hi

hope this helps

4th way using checksum

this is quick solution but depends on how your data is ? the SQL may have to change

this looks very compact and maybe what you are looking for

declare @Product varchar(20) = 'C014556'

select 
     product   
from 
   #Temp 
where 
    CHECKSUM(product) = CHECKSUM(@product)+1

image

hi

hope this helps

5th way using top 1 >

this is quick solution but depends on how your data is ? the SQL may have to change

this looks very compact and maybe what you are looking for

declare @Product varchar(20) = 'C014556'

SELECT 
    top 1 
	product
FROM 
   #Temp 
WHERE 
    product > @product

image

1 Like