A common task when working with numbers is to format them to a certain amount of decimal places. Two decimal places seems to be the most common format, but we can format to any number of decimal places we want. MySQL provides us with a number of functions that can help us achieve this.
Below are seven functions that can be used to format a number to two decimal places in MySQL.
Continue readingIn MySQL, the JSON_SCHEMA_VALID() function validates a JSON document against a JSON schema. It returns true (1) if the document is valid, and false (0) if it is not.
In SQL Server, we can use the sys.sequences system catalog view to return a list of sequence objects in the current database.
If you’re getting error Msg 3625 in SQL Server that reads “‘Inline XSD for FOR XML EXPLICIT’ is not yet implemented“, it’s because you’re trying to add an inline schema to an XML document that you’re generating using EXPLICIT mode with the FOR XML clause.
As the message alludes to, SQL Server doesn’t yet support inline XSD schemas when using EXPLICIT mode of the FOR XML clause (at least, not at the time of this writing).
To fix this issue, either use a different mode to generate the XML with an inline schema (specifically, use either AUTO or RAW mode), or don’t generate an inline schema at all (i.e. remove XMLSCHEMA from the query).
When using a function like CONVERT_TZ() to convert between time zones in MySQL, we provide the date/time value, along with the original time zone and the destination time zone (i.e. the time zone that we’re converting to).
One typical way to do this is to use the time zone offset, such as −05:00 to specify the time zone. Another method is to use the time zone name. However, this requires that we have configured named time zones in MySQL.
If you’re getting an error that reads something like “START value (101) cannot be greater than MAXVALUE (100)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s maximum possible value is lower than the start value.
To fix this issue, change either the start value or maximum value so that the maximum value is greater than the start value.
Continue readingIn MySQL, we can use the ->> operator to extract a value from a JSON document, and unquote that value.
We provide the column that contains the JSON document to the left of the operator, and the path to the value that we want to extract to the right.
The ->> operator is equivalent to JSON_UNQUOTE(JSON_EXTRACT()), and it provides the same results.
In MySQL, we can use the -> operator to extract data from a JSON document.
We provide the column that contains the JSON document to the left of the operator, and the path to the value that we want to extract to the right.
The -> operator is an alias for the two-argument form of the JSON_EXTRACT() function.
When using the FOR XML clause in a query in SQL Server, we can generate an XML document either with or without an inline schema.
To generate it with an inline XSD schema simply include the XMLSCHEMA argument. We can alternatively include the namespace if we want to specify a particular namespace.
At the time of writing, the XMLSCHEMA argument is only available when in either AUTO or RAW mode.
In MySQL, the MEMBER OF() operator tests whether or not a value is a member of a given JSON array.
It returns true (1) if the array contains the value, and false (0) if it doesn’t.
The MEMBER OF() operator was added in MySQL 8.0.17.