CODEWITHSUNDEEP
sql-server
DaveBundus
DaveBundus

Reputation: 23

SQL Server Increment all digits in column by 1

My existing column contains rows such as

`33583164+32702184+45119472+33583164+`33583164+32702184+45119472+33583164+
`6962818+11703027+22894651+34160658+4921875+6302341+47975195+4921875+32702184+21408615+`

I would like to increment each of these digits by 1 so that row 1 becomes

44694264+43813295+56230583+44694275+

Upvotes: 2

Views: 52

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

You can use TRANSLATE for this

SELECT TRANSLATE('33583164+32702184+45119472+33583164+33583164+32702184+45119472+33583164+ 6962818+11703027+22894651+34160658+4921875+6302341+47975195+4921875+32702184+21408615+', 
       '1234567890',
       '2345678901')

On versions before 2017 you can use a nested REPLACE. This needs to be ordered such that it is not replacing characters that were replacement characters in previous steps and so uses an intermediate character such as ~ so that it can distinguish between 1 that would have been created from the initial replace vs 1 in the original string (of course make sure you pick an intermediate character that you are happy will never appear in the original string).

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('0123456789'
, '0', '~')
, '9', '0')
, '8', '9')
, '7', '8')
, '6', '7')
, '5', '6')
, '4', '5')
, '3', '4')
, '2', '3')
, '1', '2')
, '~', '1')

Upvotes: 7

Related Questions