question

bhanupratapsngh9 avatar image
bhanupratapsngh9 asked

Make customer code with Country+State+CityCode 2 chars+ID

respected geeks.. Good day to you and thanks in advance I want to make a unique customer Code as i am expecting that we will make 90 Lacs unique Codes so we have three tables one for country city and state we have code there 2 chars for state and country 3 chars for city we have another table which is having unique id to which we want to hide from clients how may i accomplish this Example- customerid - 1000000201 city code - LKP state code - JP countrycode - USA we want it like this - USAJPLKP1020000001 but it must remain unique always till 90 Lacs clients
random-tag3
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Concatenating the fields together should be relatively simple `select countrycode + statecode + citycode + cast(customerid as varchar(10)` ensuring that the datatype lengths are correct, so in your example the final CustomerCode would be an 18-char field. To ensure this is unique I would use a [UNIQUE CONSTRAINT][1], but unless you can guarantee that customerid is unique within a country/state/city combination, you might need to handle what happens if a non-unique customercode is generated. [1]: http://technet.microsoft.com/en-us/library/ms190024.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bhanupratapsngh9 avatar image bhanupratapsngh9 commented ·
what is best practice to handle what happens if a non-unique customer code is generated.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
That kind of depends on you and your business. For that to happen it may be that you have duplicate customerids, so that's the problem to solve. If the combination of the constituent parts of the code are unique, then you won't have a problem.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.