Is there a way to format MySQL celldata via the column type/attributes?

Question

I am capturing 2 sets of data in 2 mysql columns:

Name       |  Number
-----------------------
Anonymous  |  123456789
anonymous  |  0123 456 789

I want to standardise the format of each column to be 1) capitalised and 2) number formated with a particular pattern:

Name       |  Number
-----------------------
Anonymous  |  0123 456 789
Anonymous  |  0123 456 789

Is there a way I can do this via setting a type/attribute in the mysql database itself (in the same way zerofill adds zeros in an INT(X) type), or do I need to format the data before entry to the database? If the latter, how would I do this with Javascript/Jquery?

Thanks

You need to manage it while storing or retriving data from database. — Suresh Kamrushi, Dec 17 '13 at 11:57
What if someone have js deactived in his browser? You're client side controls won't work! You've to consider this! Server side control of this is better — Kevin Cittadini, Dec 17 '13 at 11:58

score 0 · Accepted Answer · answered Dec 17 '13 at 12:03

0

You may fetch the data in the correct format with some useful MySQL string functions:

SELECT
    CONCAT(UCASE(SUBSTRING(t.name, 1, 1)), LCASE(SUBSTRING(t.name, 2))),
    REPLACE(FORMAT(t.number, 0), ',', ' ')
FROM table t

answered Dec 17 '13 at 12:03

VisioN

143,310
32
282
281

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

I recommend you using PHP/JS to convert to Title case.

function toTitleCase(str)
{
    return str.replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});
}

However you can read this this to learn how to convert to Title case using MySQL.

Read this to learn how to add spaces between a string using Javascript.

edited May 23 '17 at 10:32

Community

1
1

answered Dec 17 '13 at 12:04

Eisa Adil

1,743
11
16

Is there a way to format MySQL celldata via the column type/attributes?

2 Answers2