CODEWITHSUNDEEP
mysql
Kamran Ahmed
Kamran Ahmed

Reputation: 12438

To store Integers in specific format but to be treated as integers

Is it possible to store the integers in a specific format in MySQL. For example what I want to achieve is to store the primary keys (Integers with autoincrement applied to it) in a specific format i.e. 01, 02, 03 etc. Currently the primary keys are generated and stored in the format of 1, 2, 3 and so on.

What I am currently doing to achieve this formatting is fetch these numeric values from the database and format them as I need. But I was wondering if there is any built in functionality that may help me achieve this. Is there any?

Upvotes: 0

Views: 70

Answers (2)

Mohammad Hossein Amri
Mohammad Hossein Amri

Reputation: 1985

GUI/program should take care of formatting, so it consider as a bad practice to choose data type according to format.

the best way that i can suggest is use a command like this to get the desired formatting

select to_char(id,'0000') from yourtable

this way all the time when you read the id column you will get it in fixed 4 digit string, later in your program you can do a explicit or implicit convert if need to change it back to number, or let it as it is if you want to have it as string.

Upvotes: 1

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385114

Numbers are not stored in human-readable format. That would be incredibly inefficient. Numbers are stored in an internal format (probably simple two's complement binary) and then transformed into decimal (or whatever you choose) for the result of queries.

Use the string formatting functions to change this output; in this case, probably LPAD, making use of the fact that numbers can be implicitly converted to strings within queries.

SELECT `myField` FROM `myTable`
#
# myField
# -------
#    1
#    2
#    3

SELECT LPAD(`myField`, 2, '0') AS `myField` FROM `myTable`
# 
# myField
# -------
#    01
#    02
#    03

Upvotes: 0

Related Questions