11 - String Functions in MySQL

 11 - String Functions in MySQL

https://youtu.be/KRXSJb9ql1Y?si=QMf4ZqloHE7EBei6

TOTAL: 11.54

-- Now let's look at string functions. These help us change and look at strings differently.

SELECT *

FROM bakery.customers;

-- Length will give us the length of each value

SELECT LENGTH('sky');

-- Now we can see the length of each name

SELECT first_name, LENGTH(first_name)

FROM employee_demographics;

-- Upper will change all the string characters to upper case

SELECT UPPER('sky');

SELECT first_name, UPPER(first_name)

FROM employee_demographics;

-- lower will change all the string characters to lower case

SELECT LOWER('sky');

SELECT first_name, LOWER(first_name)

FROM employee_demographics;

-- Now if you have values that have white space on the front or end, we can get rid of that white space using TRIM

SELECT TRIM('sky'   );

-- Now if we have white space in the middle it doesn't work

SELECT LTRIM('     I           love          SQL');

-- There's also L trim for trimming just the left side

SELECT LTRIM('     I love SQL');

-- There's also R trim for trimming just the Right side

SELECT RTRIM('I love SQL    ');

-- Now we have Left. Left is going to allow us to take a certain amount of strings from the left hand side.

SELECT LEFT('Alexander', 4);

SELECT first_name, LEFT(first_name,4)

FROM employee_demographics;

-- Right is basically the opposite - taking it starting from the right side

SELECT RIGHT('Alexander', 6);

SELECT first_name, RIGHT(first_name,4)

FROM employee_demographics;

-- Now let's look at substring, this one I personally love and use a lot.

-- Substring allows you to specify a starting point and how many characters you want so you can take characters from anywhere in the string.

SELECT SUBSTRING('Alexander', 2, 3);

-- We could use this on phones to get the area code at the beginning.

SELECT birth_date, SUBSTRING(birth_date,1,4) as birth_year

FROM employee_demographics;

-- We can also use replace

-- Có phân biệt HOA-thường

SELECT REPLACE(first_name,'a','z')

FROM employee_demographics;

-- Next we have locate - we have 2 arguments we can use here: we can specify what we are searching for and where to search

-- It will return the position of that character in the string.

SELECT LOCATE('x', 'Alexander');

-- Now Alexander has 2 e's - what will happen if we try to locate it

-- Tìm vị trí của chữ 'e'

SELECT LOCATE('e', 'Alexander');

-- It will return the location of just the first position.

-- Let's try it on our first name

SELECT first_name, LOCATE('a',first_name)

FROM employee_demographics;

-- You can also locate longer strings

SELECT first_name, LOCATE('Mic',first_name)

FROM employee_demographics;

-- Now let's look at concatenate - it will combine the strings together

SELECT CONCAT('Alex', 'Freberg');

-- Here we can combine the first and the last name columns together

SELECT CONCAT(first_name, ' ', last_name) AS full_name

FROM employee_demographics;