SQL single row functions FAQ, Guide to customizing output using UPPER, LOWER, TO_DATE and ROUND functions

6. How does the LENGTH() function return the number of characters in a string in SQL?

The LENGTH() function counts the total number of characters in a string, including spaces. For example, LENGTH('hello') returns 5 because there are five letters. This function is really helpful when you want to check the size of text data. Imagine you want to make sure a username or a comment doesn’t get too long — LENGTH() helps you verify that easily. It’s a simple way to validate user input or control how much text gets stored or displayed.

7. Explain the use of SUBSTR() function to extract a substring from a string.

The SUBSTR() function lets you pull out a smaller part of a string. For example, SUBSTR('hello world', 1, 5) gives you 'hello'. This is super useful when you only need a portion of the text. You tell SUBSTR() where to start and how many characters you want to grab. Think about extracting an area code from a phone number or getting just the username part of an email address — SUBSTR() makes these tasks easy.

8. What is SQL's ROUND() function? How would someone use it to round a numeric value to a specific decimal place?

ROUND() rounds a number to the number of decimal places you choose. For instance, ROUND(123.456, 2) will return 123.46, rounding the number to two decimal places. This is handy when you want numbers to look cleaner or fit a certain format — like prices in a store or measurements in a report. It helps avoid long, messy decimals and makes sure your numbers are easy to read and consistent.

9. What is the use of CEIL() function in SQL and when would you use it?

The CEIL() function rounds a number up to the nearest whole number. For example, CEIL(4.3) returns 5. You’d use CEIL() anytime you want to always round up. Imagine you’re packing items into boxes and you need to figure out how many boxes to use. If there’s a partial box needed, CEIL() helps count it as a full box — because you can’t have half a box! This is perfect for situations where you don’t want to underestimate.

10. How does the SQL FLOOR() function work? What result does it give when applied to a decimal number?

FLOOR() rounds a number down to the nearest whole number. For example, FLOOR(4.7) returns 4. This function is handy when you want to avoid overestimating values. Say you want to know how many pages a set of items will take, but you want to round down to avoid counting extra pages unnecessarily. FLOOR() helps you round numbers down to be more conservative with your estimates.

11. How does the MOD() SQL function return the remainder of a division operation?

MOD() gives you the remainder after dividing one number by another. For example, MOD(10, 3) equals 1 because 10 divided by 3 leaves a remainder of 1. This function is useful in situations where the remainder matters — like checking if a number is divisible by another (if the remainder is zero) or in repeating cycles, such as rotating through a list of items or scheduling tasks.

12. What does NVL() do in SQL, and how does it replace NULL values with a specified value?

NVL() replaces NULL values with a value you choose. For example, NVL(salary, 0) changes any NULL salary values to 0. This helps keep your data clean and easier to work with. NULLs can cause issues in calculations or make reports confusing. By replacing NULLs with a default like zero, you ensure your results are complete and easier to understand.

13. What is the difference between COALESCE() and NVL() functions in SQL?

COALESCE() returns the first non-NULL value from a list of values. For example, SELECT COALESCE(NULL, 'Hello', 'World') would return 'Hello'. Unlike NVL(), which only works with two values, COALESCE() can check many values and picks the first one that’s not NULL. This makes COALESCE() more flexible and powerful when you need to handle multiple possible NULL values in your data.

14. How can you use the TO_CHAR() function to display dates as formatted strings in SQL?

The TO_CHAR() function is super handy when you want to turn a date value into a readable text format. For example, using TO_CHAR(SYSDATE, 'YYYY-MM-DD') will show today’s date like 2024-11-26. This helps a lot when you need dates in a specific style — whether for reports, user-friendly displays, or just to keep things consistent. You can customize the format to show the year, month, day, or combine them in any way that fits your needs.

15. What does the TO_DATE() function do, and how can it convert a string into a date?

When you have a date stored as text, the TO_DATE() function converts that string into a proper date format SQL can understand. For example, TO_DATE('2024-11-26', 'YYYY-MM-DD') changes the string '2024-11-26' into an actual date. This is especially useful when working with data imported from external sources or user inputs. It lets you sort, filter, or calculate date differences without errors, ensuring your database handles dates correctly and consistently.

16. How does the TO_NUMBER() function convert strings into numbers in SQL?

The TO_NUMBER() function takes a string that looks like a number and turns it into a real numeric value. For example, TO_NUMBER('1234.56') converts the text '1234.56' into the number 1234.56. This is essential when numbers are stored as text but you want to perform calculations like adding, subtracting, or averaging. Without this conversion, SQL won’t be able to do math with those values properly and may throw errors.

17. What does the TRUNC() function in SQL do? When would you use it?

The TRUNC() function cuts off a number or date to a specific decimal place or date part without rounding. For example, TRUNC(123.456, 2) returns 123.45, simply dropping the digits after two decimals. This is especially useful in financial calculations where you want to show precise values without rounding up or down. It’s also handy when working with dates — for example, removing the time portion and keeping only the date, making your results cleaner and easier to work with.

18. How can you use the REPLACE() function in SQL to substitute part of a string with another value?

The REPLACE() function swaps out all occurrences of a specified substring within a string with a new value. For example, REPLACE('Hello World', 'World', 'SQL') will return 'Hello SQL'. This function is perfect when you want to update or clean up text data in bulk. Say you have outdated terms or misspellings in customer records — with REPLACE(), you can fix them all at once instead of editing each entry manually. It’s a simple yet powerful tool for managing your text fields efficiently.

19. What is the use of the LPAD() function in SQL and how does it work?

The LPAD() function adds extra characters to the left side of a string until it reaches a certain length. For example, LPAD('123', 5, '0') returns '00123'. This is great when you want your data to have a consistent format — like padding zip codes, invoice numbers, or IDs with leading zeros. It ensures that all values line up neatly in reports or data exports, making your dataset look clean and professional.

20. How would you pad a string on the right using the RPAD() function in SQL?

The RPAD() function adds padding characters to the right side of a string to reach a desired length. For example, RPAD('Hello', 10, '*') returns 'Hello*****'. This is useful for formatting columns where fixed-length text is required. Whether it’s product codes or account numbers, RPAD() helps keep your data consistent by filling in extra spaces with characters you choose, giving your output a neat and uniform appearance.

21. What is the SIGN() function in SQL?

The SIGN() function tells you whether a number is positive, negative, or zero by returning 1, -1, or 0 respectively. For example, SIGN(-5) returns -1 and SIGN(10) returns 1. It’s especially helpful for classifying data based on its sign. In finance, for example, you could use it to quickly separate credits (positive values) from debits (negative values). It’s a simple yet powerful way to understand the direction of your numerical data.

22. What does the NVL2() SQL function do?

The NVL2() function is a powerful way to handle NULL values in your data. It checks whether an expression is NULL or not, and returns one value if it is NOT NULL, and another value if it IS NULL. For example, if you run NVL2(NULL, 'Not Null', 'Null'), it will return 'Null' because the expression is NULL.
This function is especially useful in reports or data processing when you want to clearly show different outcomes depending on whether data exists or is missing. Unlike NVL(), which only replaces NULL with one value, NVL2() gives you the flexibility to return different results for both cases, making your data handling much more precise and customizable.

23. How is the EXTRACT() function used in SQL to get parts of a date?

The EXTRACT() function is designed to pull out specific parts of a date or time, such as the year, month, day, hour, or minute. For example, EXTRACT(YEAR FROM SYSDATE) returns the current year as a number.
This function is incredibly helpful when you want to analyze or filter data by date components. Imagine you want to generate reports grouped by year or filter records to just those from a specific month — EXTRACT() makes this simple by breaking down complex date values into individual, usable parts. This helps you work with dates more flexibly and accurately in your queries.

24. How does the CONVERT() function in SQL change the data type of a value or column?

The CONVERT() function allows you to change the data type of a value or an entire column from one format to another. For example, if you have the number 123 and you want to turn it into a string, you can use CONVERT(VARCHAR, 123), which will output '123' as text.
This is very useful when your data needs to be treated differently depending on context. Say you want to apply string functions like UPPER() or SUBSTRING() to a numeric value — converting it to a string first makes this possible. It also helps prevent errors by ensuring that the data type matches the operation you want to perform, giving you more control and flexibility in your SQL queries.