user3331421 user3331421 - 4 months ago 7
SQL Question

How to fetch last character and increment it in sql

I have the following query

select case
when Right('BPUREN_3',1) Like '[A-Z]'
then concat('BPUREN','_1')
else
concat('BPUREN_3',right('BPUREN_3',1)+1)
End


I want output as BPUREN_4 But I am getting the result as BPUREN_34

Answer
select case
    when Right('BPUREN_3',1) Like '[A-Z]' then 'BPUREN_1'
    else concat(Left('BPUREN_3', 7),right('BPUREN_3',1)+1)
    End

No need to concat('BPUREN','_1') it will always be 'BPUREN_1'

You could change Left('BPUREN_3', 7) to Left('BPUREN_3', LEN('BPUREN_3') - 1) if 'BPUREN_3' could change in length (for example if its a parameter in your real code)