We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Incrementing a string Values in SQL Server

Medium Priority
470 Views
Last Modified: 2012-05-07
Hi,

How can I increment a string value as below...

CU00000001
CU00000002
CU00000003
CU00000004
CU00000005
CU00000006
CU00000007
CU00000008
CU00000009
CU00000010
CU00000011
.
.
.
CU00000019
CU00000020
.
.
.
CU00000100

How i want this is... Everytime when i insert a value I will the check the previous or highest value from the table and then I need to increment it by 1. Then I will be inserting it in the table.

Could any one help me. Thanks in advance.
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
you could set something like that in a function or maybe just a stored procedure
Jagdish DevakuSr DB Architect

Author

Commented:
Thanks Andycrofts... Your solution works.
Can i get any other method to do this. Which will not effect performance of the database. As we are planning to implement this in a very sensitive module.
there may be subtley different way to do it but the bottom line is you have to seperate the bit of the string that can be converted into a number for incremaentation before putting it back together.

The other way of doing it, if you can change the structure of your table so that the key is an auto incrementing identity field and you use a view to build the field that looks like 'CU00000101'

table
column id
id
1
2
3
.
.


view
define your string id as 'CU' + right('00000000' + cast(id as varchar(10)),8)


Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Jagdish DevakuSr DB Architect

Author

Commented:
Thanks for the support.
Thanks

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.