PostgreSQL

Question 1

What steps do you want for cleaning string data?

Answer 1

Restrict capitalization in column names
Remove extra divider spaces in column names
make column names uniform

Question 2

INITCAP()

Answer 2

SELECT INITCAPT(column)
Output: Hello Friend!
Fixes capitalization

Question 3

REPLACE()

Answer 3

SELECT REPLACE(column, string_to_replace, replacement)
SELECT REPLACE(streets, ‘Main Street’, ‘St’);
Output: Main St

Question 4

LPAD(input_string, length [, fill_value])

Answer 4

SELECT LPAD(‘column, 7, ‘X’);
OUTPUT: XXXX123
Prepending text values to a string. The fourth value has to be a string.

Question 5

Like

Answer 5

Like is used for pattern matching using wildcards such as % or _
SELECT *
FROM employees
WHERE firstname LIKE ‘Ali%’ or firstname LIKE ‘_li’

Question 6

%

Answer 6

Matches 0 or more characters

Question 7

_

Answer 7

Matches exactly 1 character
Can use multiple _ to represent more than 1 character

Question 8

REGEXP_REPLACE()

Answer 8

REGEXP_REPLACE(column, ‘regrex’, replacement, ‘g’)

‘g’ = global

Question 9

DIFFERENCE()

Answer 9

DIFFERENCE is used to compare the SOUNDEX values of two strings and returns a score between 0 and 4:
DIFFERENCE(vehicle_color, ‘GRAY’) = 4;

Question 10

SOUNDEX()

Answer 10

SOUNDEX is a phonetic algorithm used to match words or strings that sound similar in English. It converts a word to a code based on its pronunciation.
Example:
SOUNDEX(‘GRAY’) → G600
SOUNDEX(‘GREY’) → G600