SQL: Storing Formatting in Data I have always taught that it is incorrect to store formatted data in a table. Examples include:

+----------------+----+
| Data           | OK |
+----------------+----+
| 0370101234     |  ✓ |
+----------------+----+
| 03 7010 1234   |    |
+----------------+----+
| (03) 7010 1234 |    |
+----------------+----+


(Why doesn’t SO support tables?!)

The data is an Australian phone number which is generally 10 digits long, including the area code.

Including the format spacing or characters introduces ambiguity, so it is harder to sort or search.

The question is: is there anything in the standards or in relational theory which covers this issue?

EDIT

This isn’t a question about telephone numbers as such. I was illustrating a point with data that should be stored in its simplest form, even if it is to be displayed with formatting characters.

Other examples include the $ sign which is clearly never stored with a currency value, and the thousands separator which has its own issues.
Formatting is meta-data.  If you include formatting in a column, you are mixing meta-data with data, which is clearly not a great idea.  I'm not a theory-guru, but I'd bet that is listed somewhere as a no-no.

For your specific example, which I know is only an example, you may actually want to store the format in a separate table.  Below is an example for SQL Server.  I must stress, this is only an example of how you might approach the problem by abstracting the format away from the data.  Please note, I'm not saying this is the best way, or even an appropriate way for your scenario.  Simply that it MIGHT work, as in the output shown below.

IF OBJECT_ID('dbo.PhoneNumbers') IS NOT NULL
DROP TABLE dbo.PhoneNumbers;

IF OBJECT_ID('dbo.PhoneFormats') IS NOT NULL
DROP TABLE dbo.PhoneFormats;

CREATE TABLE dbo.PhoneFormats
(
    PhoneFormatID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PhoneFormats
        PRIMARY KEY CLUSTERED
    , FormatDetails varchar(30) NOT NULL
);

INSERT INTO dbo.PhoneFormats (FormatDetails)
VALUES ('(###) ###-####')
    , ('#### ###-###');

CREATE TABLE dbo.PhoneNumbers
(
    ClientID int NOT NULL
    , PhoneNumber bigint NOT NULL
    , PhoneFormatID int NOT NULL
        CONSTRAINT FK_PhoneNumber_Format
        FOREIGN KEY 
        REFERENCES dbo.PhoneFormats(PhoneFormatID)
);

INSERT INTO dbo.PhoneNumbers (ClientID, PhoneNumber, PhoneFormatID)
VALUES (1, 2125551212, 1)
    , (2, 2300123456, 2);

SELECT pn.ClientID
    , FormattedNumber = FORMAT(pn.PhoneNumber, pf.FormatDetails)
FROM dbo.PhoneNumbers pn
    INNER JOIN dbo.PhoneFormats pf ON pn.PhoneFormatID = pf.PhoneFormatID;


the output:

+----------+-----------------+
| ClientID | FormattedNumber |
+----------+-----------------+
|        1 | (212) 555-1212  |
|        2 | 2300 123-456    |
+----------+-----------------+

This can be made to support currency symbols and thousands-separators exactly the same way.