Comprehensive FAQ on SQL Conversion Functions and Conditional Expressions

Question 1

1. What is SQL's TO_CHAR() function? How do you use it to format dates and numbers?

Accepted Answer

SQL's TO_CHAR() function is primarily used in Oracle to take a date, number or timestamp and return it as a formatted string. You use this function when you want to present dates or numeric values in a specific format. You can clearly define the format b for example, the year-month-day format while usingYYYY-MM-DD. Numbers are converted to_char() with decimal places and commas at thousands place even after the addition of currency symbols. Very good to use when using in reports or where number must be stored or printed out in specific string format. It is often used for formatting the appearance of dates or numeric values in reports or output.
Oracle example:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS formatted_date FROM dual; The query will return the current date in the formatYYYY-MM-DD. The function can also be used to format numbers with specific decimal places or rounding.
Note for MySQL/PostgreSQL/SQL Server:
• MySQL uses DATE_FORMAT() for date formatting.
• PostgreSQL uses TO_CHAR() too, and supports similar date formatting functions.
• SQL Server uses formatting for date and numbers with either format() or convert().

Question 2

2. How do you use the TO_NUMBER() function in SQL? Provide an example.

Accepted Answer

The TO_NUMBER() function in Oracle is used to convert a string, date or other data types into a numeric value. It is helpful when you want to perform mathematical operations on data that may not be stored in a numeric format. For instance, if you have a column that stores numerical values in text format, like '1234' stored as a string you might use TO_NUMBER() for converting that value into an actual numeric type for calculations. You would use it in the situation where data is presented as strings but needs to be treated as numbers when doing addition, subtraction, multiplication or division operations.
Example (Oracle)
SELECT TO_NUMBER('1234') AS converted_number FROM dual;
This query takes the string '1234' and converts it to a number 1234 to be used in any kind of mathematical operations.
For MySQL/PostgreSQL/SQL Server:
• In MySQL and PostgreSQL, one may use CAST() or CONVERT() for this same effect.
• For SQL Server, the type conversions are done by the CAST() or CONVERT() functions.

Question 3

3. What is the purpose of the TO_DATE() function and how does it convert a string into a date format?

Accepted Answer

The TO_DATE() function in Oracle is used to convert a string or character value into a date format. It's especially useful when dealing with date values stored as strings in non-standard formats. This function requires a date format mask, which defines the expected structure of the date string. For instance, the date '15-08-2024' can be input as an actual DATE with the format 'DD-MM-YYYY'. This helps SQL in making a query while dealing with dates without confusions. Otherwise, using date-specific functions such as sorting or filtering is quite impossible if these kinds of strings are not converted to appropriate date formats.
Oracle example:
SELECT TO_DATE('15-08-2024', 'DD-MM-YYYY') AS formatted_date FROM dual
;That will convert the string '15-08-2024' to the actual date 15-Aug-2024.
Note for MySQL/PostgreSQL/SQL Server:
In MySQL, the equivalent is STR_TO_DATE().
In PostgreSQL, use TO_DATE(). It works similarly.
SQL Server CONVERT() can also be used for similar functionality.

Question 4

4. What is the function REGEXP_LIKE() used in Oracle? How do you implement it to validate data?

Accepted Answer

The REGEXP_LIKE() function performs pattern matching against a character string using regular expressions. Given a character string, it returns a binary value (TRUE or FALSE) indicating whether the input matches the pattern. This is one of the most efficient means to validate data especially in those situations when you require confirmation of the input with the expected format of such items as a telephone number, an email or zip code. It proves quite handy in more complicated verification processes which cannot be compared or searched using mere strings or LIKE operators.
Oracle:
SELECT employee_id
FROM employees
WHERE REGEXP_LIKE(phone_number, '^\d{3}-\d{3}-\d{4}');
This is a check to see whether the phone_number column matches a U.S. phone number pattern (such as 123-456-7890).
Note for MySQL/PostgreSQL/SQL Server:
•MySQL uses REGEXP.
•PostgreSQL uses ~ or SIMILAR TO.
•SQL Server uses PATINDEX() for regular expression-like pattern matching.

Question 5

5. How does the LISTAGG() function work in SQL? Give an example of its use?

Accepted Answer

The Oracle LISTAGG() is a function that aggregates several values from multiple rows in one string. It puts values from a specified column into one string which is separated by a defined delimiter. This function may be used when you would like to combine data from some rows into one readable value. For example, it can be used to combine employee names from the same department into a comma-separated list. The LISTAGG() allows representing aggregated data in the text form without complicated joins or subqueries.
Oracle:
SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;
In this query, employees will be grouped by their departments and their names will appear separated by commas.
MySQL/PostgreSQL/SQL Server:
•MySQL uses GROUP_CONCAT().
•PostgreSQL uses STRING_AGG().
•SQL Server uses STRING_AGG() from SQL Server 2017 onward.

Question 6

6. How does CAST() function work in SQL? Give an example of how to use it.

Accepted Answer

The CAST() function in SQL is used to convert one data type into another. This function is mostly used to ensure that data is in the right format for comparison, calculation or storage. For instance, you may want to convert a string of numeric characters into an integer to do arithmetic operations. CAST() is part of the ANSI SQL standard so it is supported in all major database systems and is therefore a portable solution for data type conversions.
Oracle
SELECT CAST('2024-08-15' AS DATE) AS converted_date FROM dual;
The above statement converts the string '2024-08-15' into DATE format and allows date-related operations.
MySQL/PostgreSQL/SQL Server
• MySQL/PostgreSQL/SQL Server all support CAST() for type conversion.
• SQL Server also has CONVERT() which provides additional formatting options for date conversions.

Question 7

7. How would you use the TO_CHAR() and TO_NUMBER() functions together to convert a date to a number format in SQL?

Accepted Answer

Oracle provides you with TO_CHAR() and TO_NUMBER() functions that you could use in combination with one another to convert a date into a string first and then the string into a numeric value. It's very useful when you would like to represent a date as a number to some calculation or comparison. You may want to have the date converted intoYYYY-MM-DD format and treated afterwards as a number to do sorting or arithmetic operations on it.
Oracle example:
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY-MM-DD')) AS numeric_date FROM dual;
This will format the current date into a string in the format ofYYYY-MM-DD, which can then be cast back to a number.
MySQL/PostgreSQL/SQL Server notes
• MySQL and PostgreSQL: Use DATE_FORMAT() and CAST().
• SQL Server: Use CONVERT() and CAST().

Question 8

8. What is the purpose of the DECODE() function in SQL? How can it be used for conditional logic?

Accepted Answer

The DECODE() function in SQL (particularly in Oracle) is applied to implement conditional logic in queries, like the IF or CASE statements in other programming languages. It compares an expression with a set of values and returns a corresponding result for the matched value. If no match is found, it defaults to a given value. This is widely used to form 'if-then' conditions in SQL queries. The function serves to ease the complexity of queries in that one will not be required to make use of complex CASE statements or nested IF conditions. For instance, DECODE() is used to make status codes more readable descriptions.
Example (Oracle):
SELECT employee_id, DECODE(department_id, 10, 'HR', 20, 'IT', 'Other') AS department_name FROM employees;
This query checks department_id and returns a custom value ('HR', 'IT', or 'Other') depending on the department.
MySQL/PostgreSQL/SQL Server Note:
• In MySQL/PostgreSQL, one uses CASE expressions for this sort of conditional logic
• SQL Server also supports CASE expressions, delivering similar functionality.

Question 9

9. What is JSON_ARRAYAGG() in SQL and how to use it?

Accepted Answer

JSON_ARRAYAGG() is an aggregate function in SQL for the newer versions of Oracle, MySQL, and PostgreSQL. It takes multiple rows of data and returns them as a JSON array. This function is mainly applied whenever you need to convert results from a query to a JSON array format. This is very helpful when using RESTful APIs and applications and where data needs to be returned as a single JSON object that can be processed. As part of SQL's overall support for working with JSON data types, it returns results in a structured JSON.
Oracle Example:
SELECT department_id, JSON_ARRAYAGG(employee_name) AS employee_names FROM employees
GROUP BY department_id;
It groups employee names into an array of JSON sorted by department.
MySQL/PostgreSQL/SQL Server Notes
• MySQL uses JSON_ARRAYAGG().
• PostgreSQL uses JSON_AGG().
• SQL Server uses FOR JSON PATH to return results as JSON.

Question 10

10. How do you think the EXTRACT() function in SQL works? Provide an example of how you could use it to extract a certain part of a date.

Accepted Answer

SQL uses the EXTRACT() function to fetch a particular part of a date or time. It lets you extract year, month, day, hour, minute or even seconds from a date-time value. This is particularly handy when you need to extract or perform some operations where you are filtering or looking for specific parts of the date like all records by a certain year or by a certain month. This function can be used with either DATE, TIME or even TIMESTAMP columns.
Oracle example:
SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual;
Note: This would return all the year parts of the actual system date from SYSDATE.
Note for MySQL/PostgreSQL/SQL Server:
• The MySQL equivalent function is YEAR(), MONTH(), or DAY().
• Both PostgreSQL and SQL Server support EXTRACT().

Question 11

11. What is SQL COLLECT() function? How do you use it to collect data into a collection?

Accepted Answer

Oracle's SQL COLLECT() function collects multiple rows of data into a collection or nested table. It's extremely useful if you have a need to group related rows into a structured collection that can then be used further for manipulation or other types of processing. It returns a collection type, thus it allows holding multiple values within a single variable. It's very frequently used whenever dealing with Oracle collections or any operation requiring nested data.
Oracle Example:
SELECT department_id, COLLECT(employee_name) AS employee_names FROM employees
GROUP BY department_id;
This query groups employee names by department and returns the results as a collection of names for each department.
Note for MySQL/PostgreSQL/SQL Server:
• MySQL and PostgreSQL do not directly support COLLECT(). In those systems, similar results can be achieved using ARRAY_AGG() or GROUP_CONCAT().
• SQL Server has no COLLECT() function but provides FOR XML PATH and STRING_AGG() for collecting data to a collection-like format.

Question 12

12. Describe the REPLACE() function in SQL. Provide an example where you replace part of the string.

Accepted Answer

The REPLACE() function in SQL is used to search for a substring within a string and replace it with a new substring. It is a very versatile function commonly used to clean data, modify strings or even format text by replacing specific characters or words. This function is case-sensitive so the exact match of characters is replaced. It's especially useful in data transformation and string manipulation tasks.
Example (Oracle):
SELECT REPLACE('Hello, World!', 'World', 'Oracle') AS new_string FROM dual;
This will replace 'World' with 'Oracle': Hello, Oracle!
This is specific for MySQL/PostgreSQL/SQL Server:
• All systems support the function: there is no change in the way it works in syntax neither.

Question 13

13. How CAST() function used in SQL to change the data type?

Accepted Answer

The CAST() function of SQL is used to change from one data type to another. A common use of the function, because you may need to alter it for comparison, calculation or a variety of operations that you can run on the data. This could be converting string into number so that a math operation can be done or number into string in order to be output or displayed in a specific fashion.
Example (Oracle):
SELECT CAST('1234' AS NUMBER) AS cast_number FROM dual;
This will cast the string '1234' into a number.
Note for MySQL/PostgreSQL/SQL Server:
• The CAST() works similarly in MySQL and PostgreSQL.
•SQL Server also supports CAST() and CONVERT() for type conversions, with CONVERT() offering more format control for date and time conversions.

Question 14

14. How would you use XMLAGG() in SQL to aggregate XML data?

Accepted Answer

SQL aggregation function that aggregates multiple XML rows to a single document is referred to as the XMLAGG() function. When you need to integrate several single XML fragments to a very large single document, which is mostly essential in your work when applying XML in databases, XMLAGG() functions perform this for you based on the used delimiter or the specified structure. It also helps preserve the format for the purpose of integrating those pieces to form the one single XML document.
Example (Oracle):
SELECT department_id, XMLAGG(XMLELEMENT(NAME employee, employee_name)).getClobVal() AS employees_xml FROM employees
GROUP BY department_id;
This query groups employee names into an XML format.
Note for MySQL/PostgreSQL/SQL Server:
•MySQL doesn't have XMLAGG(), but XML functions like GROUP_CONCAT() or CONCAT() can be used in similar scenarios.
•PostgreSQL supports XMLAGG(), which works similarly.
•SQL Server uses FOR XML PATH to achieve similar XML aggregation.

Question 15

15. What is the purpose of the TO_CHAR() function in SQL and how can it be used to format numbers or dates?

Accepted Answer

The TO_CHAR() function in SQL is used to convert a value (such as a date, number or timestamp) into a formatted string. This function is useful especially when you want to print numbers or dates in a certain format, for example, dates in the DD-MM-YYYY format or conversion of numbers with certain decimal places. You can set up your output format as needed. You can output dates as the year only or the month only. You can output numbers as currency or as a percentage.
Example (Oracle):
SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') AS formatted_date FROM dual;
This statement converts the system date to the format DD-MM-YYYY.
Note for MySQL/PostgreSQL/SQL Server:
•MySQL uses DATE_FORMAT() function for the same purpose.
•PostgreSQL uses TO_CHAR() as in Oracle.
•SQL Server uses FORMAT() for formatting dates and numbers.

Question 16

16. What does SQL's TO_NUMBER() function do and how does it work?

Accepted Answer

SQL TO_NUMBER() function is applied when a character or any other type of data needs to be converted to a numeric number. For example, in some computations you have a variable represented as text but would still like to execute certain computations with such data while sometimes your data comes in as a mixture of different types. This function can further format the string, dealing with decimal, currency symbols or commas in the input string to ensure that the string value was correctly converted into a number to perform mathematical operations on it.
Example (Oracle):
SELECT TO_NUMBER('1234.56', '9999.99') AS converted_number FROM dual;
This statement converts the string '1234.56' into a numeric value.
Notice for MySQL/PostgreSQL/SQL Server:
•CAST() or CONVERT() could be used for numeric casting in MySQL.
• PostgreSQL supports CAST() to convert strings to numbers.
• SQL Server uses CAST() or CONVERT() for the same purpose.

Question 17

17. What is the SQL TO_DATE() function and how does it convert a character string into a date?

Accepted Answer

The SQL TO_DATE() function converts a string into a date format. It can be very helpful when the data is stored as text but needs to be treated as a date for further processing, like comparing or calculating dates. You can specify the format of an input string to ensure that the string is read and turned into the desired date format. The TO_DATE() function is often used when bringing in or cleaning data that has been imported from a source outside of your program where date formats may vary.
Oracle
SELECT TO_DATE('2024-11-01', 'YYYY-MM-DD') AS converted_date FROM dual;
In this query, the string '2024-11-01' is transformed into a date format.
Note for MySQL/PostgreSQL/SQL Server:
STR_TO_DATE() is available in MySQL.
•PostgreSQL uses TO_DATE() with similar syntax.
•SQL Server uses CONVERT() for date conversions.

Question 18

18. How do you make use of the LISTAGG() function in SQL and when is it valuable?

Accepted Answer

The function LISTAGG() SQL aggregates multiple rows of information into a single string and it is separated by using a specified delimiter. Especially when you want to accumulate values from different rows for individual outputs. For instance, when you want to list all the names of employees in a department or all product names in a category, LISTAGG() comes in handy as it will help you do this aggregation in an efficient manner. It is also widely used in reporting and generating comma-separated lists.
Oracle
SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees_list FROM employees
GROUP BY department_id;
This statement groups employee names by department in a comma-separated list.
MySQL/PostgreSQL/SQL Server
•MySQL achieves the same functionality with GROUP_CONCAT().
•PostgreSQL uses STRING_AGG().
•SQL Server achieves similar string aggregation with STRING_AGG().

Question 19

19. What is the SQL REGEXP_REPLACE() function and how does it work with pattern-based replacements?

Accepted Answer

The REGEXP_REPLACE() function in SQL is used for pattern-based string replacement using regular expressions. It allows you to search for a specific pattern in a string and replace it with a new value. It may be used for text-data cleaning or transformation. Its function can be used for deletion of unwanted characters, replace certain words and formatting according to complex patterns in a string and thus is very powerful when a series of data manipulation needs to be done.
Example(Oracle):
SELECT REGEXP_REPLACE('abc123xyz', '\d', '#') AS replaced_string FROM dual;
This statement replaced all digits in the character string 'abc123xyz' with # so it returns 'abc###xyz'.
Note for MySQL/PostgreSQL/SQL Server:
•MySQL supports pattern-based replacement using REGEXP_REPLACE().
•PostgreSQL supports REGEXP_REPLACE() with similar functionality.
•SQL Server supports TRANSLATE() and PATINDEX() for pattern-based replacements, though it does not natively support REGEXP_REPLACE().

Question 20

20. What is SQL CAST() function? How is it different from the CONVERT() function?

Accepted Answer

A CAST() function in SQL takes one data type and gives another data type. That is why it is quite often used for converting some data type in order to conduct operations such as mathematic calculations, concatenation or comparison. The main distinction between CAST() and CONVERT() functions is found primarily in specific implementations by separate databases. While CAST() is an ANSI SQL standard and has the same syntax everywhere, CONVERT() is specific to SQL Server and offers more flexibility in formatting especially in date/time conversions.
Example (Oracle):
SELECT CAST('2024' AS NUMBER) AS converted_number FROM dual;
This statement converts the string '2024' into a number.
Note for MySQL/PostgreSQL/SQL Server:
•MySQL and PostgreSQL use CAST() for type conversions.
•SQL Server supports both CAST() and CONVERT(), but CONVERT() has more flexibility in date formats.

Question 21

21. How would you use the TO_CHAR() function to print a numeric value with commas for thousands?

Accepted Answer

SQL TO_CHAR() function is used to format numeric values in a specific way. You can include adding commas for thousands, for example. It's very helpful when numbers need to be presented in an understandable manner like reports or user interfaces. The format mask you want can be included in the TO_CHAR() function to determine the look of the number. It's more presentable and clear, especially when the numbers are large.
Example (Oracle):
SELECT TO_CHAR(1234567.89, '9,999,999.99') AS formatted_number FROM dual;
The query formats numbers 1234567.89 as 1,234,567.89.
MySQL/PostgreSQL/SQL Server Note:
•MySQL uses FORMAT() for the same formatting.
•PostgreSQL uses TO_CHAR() with the same syntax as Oracle.
•SQL Server uses FORMAT() to apply formatting.

Question 22

22. Explain the SQL DECODE() function and how it can be used in conditional statements.

Accepted Answer

The SQL DECODE() function performs IF-ELSE logic of a query in a neat and tidy format. This function compares an expression against a list of values and it returns the matched value for the first matched value. It will return the default value if no value is matched. The DECODE() function can be very useful to convert numeric codes into meaningful text labels or even to perform conditional logic straight within SQL queries thus avoiding the need to fallback to CASE statements or added logic layers.
Oracle Syntax
SELECT employee_id, DECODE(department_id, 10, 'HR', 20, 'IT', 'Other') AS department_name FROM employees;
This statement will return 'HR' when department_id is 10 and 'IT' when it is 20 and will return 'Other' otherwise for all other department_ids.
Note for MySQL/PostgreSQL/SQL Server
•MySQL and PostgreSQL do not support DECODE() but can use CASE WHEN for similar functionality.
•SQL Server supports CASE as the primary method for conditional logic.

Question 23

23. What is the EXTRACT() function in SQL and how does it extract parts of a date?

Accepted Answer

The EXTRACT() function in SQL extracts specific components of a date such as year, month, day, hour, minute or second. This is useful when you want to analyze or compare specific parts of a date. Perhaps you might want to get all orders that were made in some certain month or determine the ages of people based on a date of birth. In this case, the date components can be accessed in a query directly with help of the EXTRACT() function.
Example (Oracle):
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;
This query fetches the year from hire_date column.
Note for MySQL/PostgreSQL/SQL Server:
•MySQL utilizes YEAR(), MONTH() and DAY() to extract date's portion.
•PostgreSQL does support EXTRACT() too with very much similarity to Oracle
• SQL Server uses DATEPART() to extract any of date's components.

Question 24

24. How does CAST() differ from CONVERT() in SQL?

Accepted Answer

The CAST() and CONVERT() functions are used to change the data type of any field to another. CAST() is an ANSI SQL standard which most database systems have the same implementation for while CONVERT() is SQL Server specific that gives extra functionality like date time formatting in its output. CAST() is generally for simple type conversions and CONVERT() is more versatile especially with date and string conversions in SQL Server.
Use Case (SQL Server):
SELECT CAST('2024-11-01' AS DATETIME) AS converted_datetime FROM dual;
This is how you convert a string to DATETIME.
Note for MySQL/PostgreSQL/SQL Server
• In MySQL you should use CAST() or CONVERT() depending on the function to be used.
•PostgreSQL uses CAST() however does not support CONVERT()
• SQL Server supports both, and CONVERT() offers many more formatting options.

24 Most Asked SQL Qquestions on Conversion Function and Conditional Expression

Get in touch