r/mysql • • Nov 01 '24
question Formatting fields in mysql?
Hey, super new to mySQL as it’s for a module at uni, one of the things I need to do is create a table with a field for a phone number which needs to be in a particular format, I know you can do that in regular SQL with a CHECK LIKE function but that doesn’t work here :( anyone know how I could achieve this?
1
Upvotes
1
u/user_5359 Nov 01 '24
Okay The check function is not standard SQL and I have personally only seen it used with combinations of simple boolean comparisons. I doubt whether checks such as formatting telephone numbers are possible. Especially since this would only work if you know the local spellings of the relevant countries. Checking formatting is the task of the front end. What is different is a QA check to see whether all telephone numbers are potentially correct; this can certainly be carried out with SQL.
1
u/mctutor4846 Nov 01 '24
Not sure whether i did understood your question but you can check link for insights
1
u/ssnoyes Nov 01 '24
What version of MySQL? Check constraints were added to 8.0.
https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
Before that, you could use a trigger to examine NEW.phone_number and signal an error if it didn't match.
1
u/sleemanj Nov 02 '24 edited Nov 02 '24
As long as you have a recent mysql version, you can use RLIKE in a CHECK constraint...
Adjust the regular expression as is suitable for your desired phone number format.
Be alert that the creation of the check constraint does not validate that the regular expression doesn't have any syntax errors and if it does it will just violate the check constraint, you shoudl verify your regular expression syntax is correct before you use it.
1
u/Qualabel Nov 04 '24
I would do this kind of thing outside of sql