CODEWITHSUNDEEP
sqldatabase-design
JLawrence
JLawrence

Reputation: 192

How to prefix int data type value with leading zeroes?

would it be possible to set an int value to begin with a zero? example would be like an ID number which would be 0020192 but it only saves it as 20192, i guess it would be possible to set it as a string related type but if possible let it remain as Int.

the 00 would be required cause the first 3 digits(002) denote an employee work section while the last 4 would be the employee's actual number(0192, the 192nd employee)

Upvotes: 1

Views: 3009

Answers (3)

mu is too short
mu is too short

Reputation: 434835

In your comment you say this:

actually it would be a seven digit id. 0090001, the first three digits (009) would identify the workstation of an employee while the last 4 digits(0014) would be his actual employee id.

That says that your IDs aren't actually numbers at all, they're strings with a specific internal structure:

  1. Seven characters long.
  2. All characters are digits.
  3. The first three characters identify a workstation.
  4. The last four characters identify an employee.

That's a string that just happens to look like a number. You should use a char(7) for this and add a CHECK constraint to enforce the internal format (or as much of it as you can get away with).

I also don't see much chance of doing any arithmetic or other number-ish things with these IDs, mostly because they are not, in fact, numbers.

A happy side effect of using char(7) for this is that your application should treat the IDs as strings and you won't have to worry about something getting confused and thinking that 0112222 is an octal number.

If possible you should use two separate string columns:

  1. A char(3) for the workstation ID.
  2. And a char(4) for the employee ID.

If you have these two objects elsewhere in your database using numeric IDs then use two numbers here as well and add foreign keys to constraint your data.

Executive summary: Your IDs are not numbers so don't try to store them as numbers, they're strings that look like numbers and should be stored and manipulated as strings.

Upvotes: 4

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

only a display issue.

if your IDNO(autoincrement/int) in the database is 201 and you want it to be displayed to a 10 character long format e.g. 0000000201

should be like this.

select rtrim(replicate('0',abs(len(IDNO)-10)) + CAST(IDNO as varchar)) from IDMaster

just modify 10 on your preferred length of display. e.g. 2 , so abs(len(IDNO)-2)

cheers.

Upvotes: 0

AKZap
AKZap

Reputation: 1211

just try this code

SELECT RIGHT(CONCAT('0000000', 201920),8) AS ID_Number

http://sqlfiddle.com/#!2/7215e/1

Upvotes: 2

Related Questions