SQL offers a variety of built-in functions to manipulate text, handle dates, and perform mathematical calculations. These functions are widely used for data analysis and transformation.
String Functions in SQL
String functions help manipulate and format text data stored in your database.
UPPER: Convert text to uppercase.
This query will convert first_name values to uppercase.
Practice: Write a query to convert client_name values to uppercase from clients table.
LOWER: Convert text to lowercase.
This query will convert first_name values to lowercase.
Practice: Write a query to convert client_name values to lowercase from clients table.
CONCAT: Join multiple strings into a single string.
This query will combine first_name and last_name into a single string with a space.
Practice: Write a query to combine client_name and industry into a single string from clients table.
TRIM: Removes spaces in the text.
This query will remove leading and trailing spaces from a string.
Practice: Remove leading and trailing space from the text " SQL Functions ".
SUBSTRING: Extract part of a string
This query will extract the first 3 characters from first_name.
Practice: Write a query to extract first 2 characters from client_name in clients table.
Date Functions in SQL
Date functions help retrieve and manipulate date and time values.
Current date only
Add days, months, years
Extract the year
Mathematical Functions in SQL
ROUND: Round to the nearest number.
Rounds 123.456 to 2 decimal places.
Practice: Write a query to round 392.092848 to 4 decimal places.
CEIL: Round up the number.
Rounds 123.1 up to 124.
Practice: Write a query to round up the number 344.2
FLOOR: Round down the number.
Rounds 123.9 down to 123.
Practice: Write a query to round down the number 344.2
MOD: Get remainder of the divison.
Returns the remainder when 10 is divided by 3.
Practice: Write a query to find remainder when 47 divided by 4.
For a deeper dive and more learning about SQL functions, visit the official documentation:
- MySQL Functions: https://dev.mysql.com/doc/refman/8.4/en/functions.html
- PostgreSQL Functions: https://www.postgresql.org/docs/current/functions.html
FAQs
1.What is the purpose of string functions in SQL?
String functions are used to manipulate and process textual data. They allow you to transform, search, format, and extract information from strings, making it easier to work with names, addresses, and other character-based fields in a database.
2. What is the difference between CONCAT and CONCAT_WS in SQL?
- CONCAT joins strings directly.
- CONCAT_WS (with separator) joins strings using a specified delimiter. .
3. What is the difference between ROUND, CEIL, and FLOOR in SQL?
- ROUND: Rounds a number to the nearest value.
- CEIL: Always rounds up.
- FLOOR: Always rounds down.
4. How can I calculate the number of days between two dates in SQL?
Use DATEDIFF function for this purpose.
5. Can SQL functions be combined in one query?
Yes. You can nest and chain functions.
SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS full_name FROM employees;
This query concatenates first and last names, then converts the result to uppercase.