Some Cleansing Techniques in SQL.

Data in the real world is virtually always a mess. Whether you need to find facts about data as a data scientist, data analyst, or even a developer, you must first guarantee that the data is clean enough.

1. TRIM(): This function removes leading and trailing spaces from a string.

The TRIM() function eliminates the space character OR additional characters provided from the beginning or end of a string.

2. COALESCE: This function is used to handle null values. This function replaces your null values with the desired input.

3. TRUNC(): truncates an integer to the number of decimal places specified.

4. EXTRACT(): extracts a portion of a date, such as the month, day, or hour.

5. CONVERT () or CAST(): This function converts a value (of any type) to a given datatype.

6. INITCAP(): This function substitutes the first letter of each word in a character string with an uppercase letter.

7. UPPER() and LOWER(): converts text to all uppercase or lowercase letters.

8. SUBSTRING (): This function extracts some characters from a string. The SUBSTRING() method extracts a substring from any string.

9. UNNNEST(): in PostgreSQL, creates a table structure from an array and turns the array into rows.

There are more advanced approaches for dealing with more complex data cleansing challenges, but for the time being, we will stick with the basics.