CODEWITHSUNDEEP
sqlsql-server
marko
marko

Reputation: 517

how to increase the max value keeping string lenght

I have a column numbers from 1 to 10000, they are stored like string with 8 characters, so number 1 is stored like 00000001 and 10000 is stored like 00010000. I need to take the max for this column and increase by 1 in insert operations. but if I do:

Max(column)+1

I lost previous 0, so I have 10001 and not 00010001, how I can keep zeros?

Upvotes: 0

Views: 72

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

you could try using cast and replicate

  replicate('0', 8 - CONVERT(varchar(8),  max(CAST(column AS INT)) +1) ) + CONVERT(varchar(8),  max(CAST(column AS INT)) +1)

anyway you should take a look for identity in sqlserver for manage autoincrement column automatically

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

You can use the FORMAT function to format numbers:

SELECT FORMAT(1, '00000000') -- 00000001

Having said that, you should be storing numbers inside the database as-is and format them in the application.

Upvotes: 1

Related Questions