Add 1 to a varchar?

Transact-SQL
1

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

2

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
3

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
4

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