Subtract from alphanumeric string value 1

[747576]

I would like some help with the following query.

The sql is designed to subtract 1 from the aplhanumeric value in the column ID and show the result in column [PREV_ID]

The combination of numeric and non-numeric characters can vary.

For example the prefix can vary like:

BD001

or

1234/01c


The last numeric digit is the one that gets incremented and in the case of the query below should be subtracted by 1.

The sql works fine until the number after the prefix is equal to 0 or 00

in these cases there is no previous id so the ID should just be copied over or made null.
Currently if ID = 1_0 the result is 0_0, it would be good if it was just kept as 1_0.

The sql also doesn't work where the number after the prefix is a multiple of 10, the sql seems to ignore the last numeric digit if it is 0, for example:

if ID = '4235_20d' the query currently produces the prev ID as '4235_10d' when it should be '4235_10d'.

Can the current sql be modified to fix the problems.

Thank you

DECLARE @Temp TABLE (ID VARCHAR(50),Type VARCHAR(50))
INSERT INTO @Temp VALUES ('BD001','BD')
INSERT INTO @Temp VALUES ('BD002','BD')
INSERT INTO @Temp VALUES ('BD003','BD')
INSERT INTO @Temp VALUES ('BD004','BD')
INSERT INTO @Temp VALUES ('BD005','BD')
INSERT INTO @Temp VALUES ('1234/01c','c')
INSERT INTO @Temp VALUES ('1234/02c','c')
INSERT INTO @Temp VALUES ('1234/03c','c')
INSERT INTO @Temp VALUES ('1234/04c','c')
INSERT INTO @Temp VALUES ('4235_01d','d')
INSERT INTO @Temp VALUES ('4235_02d','d')
INSERT INTO @Temp VALUES ('4235_03d','d')
INSERT INTO @Temp VALUES ('4231_0','')
INSERT INTO @Temp VALUES ('1_0','')
INSERT INTO @Temp VALUES ('100_01','')
INSERT INTO @Temp VALUES ('4235_20d','d')
INSERT INTO @Temp VALUES ('123456','d')


select ID
       ,case 
                when patindex('%[1-9]%', reverse(CS.ID))>0 
				then 
                        reverse( 
								stuff( 
									reverse(
											CS.ID)
											, patindex('%[1-9]%', reverse(CS.ID)
											)
									,1
									, cast(
											substring(
												reverse(
														CS.ID
														) 
														,patindex(
																'%[1-9]%', reverse(CS.ID)
																	)
													  , 1)-1 as varchar
											)
									) )
                else
                        null
        end AS [PREV_ID]
 from 
 
@Temp CS

 

[747576]

mistake in message, should be -

"if ID = '4235_20d' the query currently produces the prev ID as '4235_10d' when it should be '4235_19d'.

 

[sqlprograms]

Try this one,

DECLARE @Temp TABLE (ID VARCHAR(50),Type VARCHAR(50))
INSERT INTO @Temp VALUES ('BD001','BD')
INSERT INTO @Temp VALUES ('BD002','BD')
INSERT INTO @Temp VALUES ('BD003','BD')
INSERT INTO @Temp VALUES ('BD004','BD')
INSERT INTO @Temp VALUES ('BD005','BD')
INSERT INTO @Temp VALUES ('1234/01c','c')
INSERT INTO @Temp VALUES ('1234/02c','c')
INSERT INTO @Temp VALUES ('1234/03c','c')
INSERT INTO @Temp VALUES ('1234/04c','c')
INSERT INTO @Temp VALUES ('4235_01d','d')
INSERT INTO @Temp VALUES ('4235_02d','d')
INSERT INTO @Temp VALUES ('4235_03d','d')
INSERT INTO @Temp VALUES ('4231_0','')
INSERT INTO @Temp VALUES ('1_0','')
INSERT INTO @Temp VALUES ('100_01','')
INSERT INTO @Temp VALUES ('4235_20d','d')
INSERT INTO @Temp VALUES ('123456','d')


select ID,
--patindex('%[1-9]%', reverse(CS.ID)),patindex('%[0-9]%', reverse(CS.ID)),

case when patindex('%[1-9]%', reverse(CS.ID))=patindex('%[0-9]%', reverse(CS.ID))
then
reverse(stuff(reverse(CS.ID), patindex('%[1-9]%', reverse(CS.ID)
        ),1, cast(substring(reverse(CS.ID),patindex('%[1-9]%', reverse(CS.ID)
         ), 1)-1 as varchar)))
else
	case when left(reverse(stuff(reverse(CS.ID), patindex('%[1-9]%', reverse(CS.ID)
        ),1, cast(substring(reverse(CS.ID),patindex('%[1-9]%', reverse(CS.ID)
         ), 1)-1 as varchar))),1)=0 then CS.ID
		when cast(substring(reverse(CS.ID),patindex('%[1-9]%', reverse(CS.ID)
         ), 1)-1 as varchar)=0 then 
		reverse(stuff(reverse(CS.ID), patindex('%[1-9]%', reverse(CS.ID)
        ),1, cast(substring(reverse(CS.ID),patindex('%[1-9]%', reverse(CS.ID)
         ), 1)-1 as varchar)))
	else
		reverse(stuff(reverse(CS.ID), patindex('%[1-9]%', reverse(CS.ID)
        )-1,2, cast(
			reverse(reverse(substring(reverse(CS.ID),patindex('%[1-9]%', reverse(CS.ID))-1,2))-1)
		as varchar)))
		
	end 


end

 from @Temp CS

SQL Server Programmers

http://www.sql-programmers.com/