Data Types used in SQLite

Question

I'm confused what to declare as datatype in SQLite for the following: 1.email 2.phone number 3.password(Some articles I find that they use String instead of int for phone number and password , why is it so?)

Use **string for all** 3 – Phantômaxx Dec 31 '13 at 07:32 — Phantômaxx, Dec 31 '13 at 07:32

score 9 · Answer 1 · answered Dec 31 '13 at 02:26

There are fundamentally 5 types of datatypes in SQLite:

1.) NULL 2.) BLOB 3.) INTEGER 4.) REAL 5.) TEXT

You can read them up on the following page SQLite Datatypes.

As for your question :

1.) Since Email can contain alphanumeric and special characters, this field has to be declared as TEXT.

2.)For a phone number , you have to decide as to how to store the numbers. For e.g. one of the guys would want his phone number to be stored as

4567890

While some other guy would want the same number to be stored as

456-7890

In the first case , the datatype would be INTEGER while in the second case it would TEXT as the second case consists of special character.

3.) For password , use TEXT as it can have any character. Be sure to encrypt it though.

Hope it helps. :)

Avoid INTEGER for telephone numbers - any leading 0 will be dropped, i.e. 0114 will become 114. You should use TEXT, but validate the number separately. — AndyS, May 11 '21 at 16:03

score 2 · Answer 2 · answered Dec 31 '13 at 02:22

2

The reason behind using string is that, Sqlite was made much flexible and dynamic.

Check this link: http://www.sqlite.org/datatype3.html

First two paragraphs answers all of your questions.

answered Dec 31 '13 at 02:22

Chintan Soni

24,761
25
106
174

score 0 · Answer 3 · edited Sep 02 '14 at 03:17

Use String for all three.

String enables the use of the full character set, including lower case, upper case, numerals and special characters. Each of the examples you give (email, phone number and password) will use elements from the full character set. (email '@', phone '-' & '()' and passwords are more effective the larger the character set chosen from.)

Strings may also be parsed with Regular Expressions to validate them and achieve database wide conformity. For example you may choose a consistent format for phone numbers of '++64 4 12345678' and use RE's to achieve that state before saving it to the database. Equally you might use REs to strip all extra characters and save the phone number as an int.

Passwords using just int, have a character base of 10 characters, the full character set has (26+26+10+33)=95 characters. If your password is 8 characters long, that is 10^8 or 100,000,000 combinations (trivial to brute force) or 95^8 which a heck of a lot more (getting a lot more difficult to brute force).

Data Types used in SQLite

3 Answers3