user3331421 user3331421 - 1 year ago 104
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')

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

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

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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download