I love when you specify when you actually use which functions at your real world job and when functions are less used. Gives a good idea of what is to come in a job
string functions are used to manipulate and work with text strings. common string functions with real-world uses: - CONCAT(): Combines multiple strings into one (e.g., full names). - LENGTH(): Returns the length of a string (e.g., checking password length). - LOWER()/UPPER(): Converts a string to lowercase or uppercase (e.g., email validation). - SUBSTRING(): Extracts part of a string (e.g., area codes). - TRIM(): Removes extra spaces (e.g., cleaning user input). - REPLACE(): Replaces occurrences of a substring (e.g., updating city names).
**Summary:** - `length(str)`: (int) Length of `str`. - `upper(str)`: (str) `str` converted to uppercase. - `lower(str)`: (str) `str` converted to lowercase. - `trim(str)`: (str) `str` trimmed from both ends. - `rtrim(str)`: (str) `str` trimmed from the right. - `ltrim(str)`: (str) `str` trimmed from the left. - `left(str, n)`: (str) The first `n` characters of `str`. - `right(str, n)`: (str) The last `n` characters of `str. - `substring(str, n, m)` (also `substr()`): (str) An `m`-long substring starting at the `n`-th character of `str`. - `replace(str, sub1, sub2)`: (str) `str` with all instances of `sub1` replaced with `sub2`. - `locate(sub, str)`: (int) Position of `sub` in `str` (1-based index). - `concat(str1, str2, ...)`: (str) Concatenation of `str1` and `str2`. ------------------------------------ **Bonus:** - `instr(str, sub)`: (int) Position of `sub` in `str` (1-based index). ---------------------------------------- Please let me know if there is anything wrong. Thanks!
Hi i know its been long, i really hope i get a response, i have been following up to this point. 8:57 of this video, REPLACE String function, you used 'A' to be replaced with 'Z', but the name "Ann", the 'A' wasn't replaced. why?
Hmmm... Wonder how I managed to get my comment about the UNION lesson in the String Functions lesson???? Guess I should have been paying more attention to detail...! That said, this lesson on String Functions of succinct and clear. Everything works as advertised and it all makes sense. Nice job Alex!
you could use a double replace to do that, like this: select first_name, replace(replace(first_name, 'a','z'), 'A', 'Z' ) as modified_name from employee_demographics ; think about it as you have your first replace based on first_name, then on the outer replace it takes the inner replace word which is only for lowercase a's and in those words it starts replacing the uppercase letter
Hi Alex, I want to start learning Data analysis and the laptop I chose to buy has a i5 13420h cpu (16gb ram, 1tb ssd) What do you think about the cpu? is it good enough for me to get started?
Hi Alex, i am having 10 years of experience as a Quality analyst in banking,can you please help me to suggest if self-study or doing a master's is a good option
Well, that was fun! Ending my day with Alex the Analyst while my lovely Bride is preparing dinner (I'm one lucky 'Wanna-Be-Data-Nerd'). Somehow UNION just makes more sense than JOINS for me. I just need more exposure and practice - like this Intermediate MySQL Series for example. Thanks again Alex! 😎
@@mdfuadhasancanada bro, I wish I could come here earlier. I searched for similar solution online multiple times but could not figure it out. thanks for sharing the solution.
No issues as long as it doesn't give you a problem lol, as someone said, don't fix it if its not broken, but on other platforms, writing it in capitals help the most as much as I know
use substring for first letter with upper and for remaining letters ,use substring with lower and then concat both. for example:Name :ALEX select name, concat( (upper(substring(name,1,1)), (lower(substring,(name,2,length(name)-1) ) #lower(substring(name,2)) you can also use this ) as new_name from table1;
I love when you specify when you actually use which functions at your real world job and when functions are less used. Gives a good idea of what is to come in a job
string functions are used to manipulate and work with text strings.
common string functions with real-world uses:
- CONCAT(): Combines multiple strings into one (e.g., full names).
- LENGTH(): Returns the length of a string (e.g., checking password length).
- LOWER()/UPPER(): Converts a string to lowercase or uppercase (e.g., email validation).
- SUBSTRING(): Extracts part of a string (e.g., area codes).
- TRIM(): Removes extra spaces (e.g., cleaning user input).
- REPLACE(): Replaces occurrences of a substring (e.g., updating city names).
Starting the day with a cup of coffee and Alex the Analyst.
Feeling good about today.
so did u finish it did u land a job want some update
Awesome session. I love the functionalities of these strings in data cleaning. Thanks Alex!
Very effective lesson with direct key points. Really appreciate your effort, Alex
Thank you Alex. Just wanted to drop a quick note to say how much i loved your video
Sorry one question, why in your roadmap abou data analyst you dont talk about Statistics skills. Thanks
Also did we have to learn both power bi and tableau
You are a great teacher, Alex. Wish you all the best.
Hi, Alex. Appreciate all you work here. Could you make a video regarding procedures and some use cases?
**Summary:**
- `length(str)`: (int) Length of `str`.
- `upper(str)`: (str) `str` converted to uppercase.
- `lower(str)`: (str) `str` converted to lowercase.
- `trim(str)`: (str) `str` trimmed from both ends.
- `rtrim(str)`: (str) `str` trimmed from the right.
- `ltrim(str)`: (str) `str` trimmed from the left.
- `left(str, n)`: (str) The first `n` characters of `str`.
- `right(str, n)`: (str) The last `n` characters of `str.
- `substring(str, n, m)` (also `substr()`): (str) An `m`-long substring starting at the `n`-th character of `str`.
- `replace(str, sub1, sub2)`: (str) `str` with all instances of `sub1` replaced with `sub2`.
- `locate(sub, str)`: (int) Position of `sub` in `str` (1-based index).
- `concat(str1, str2, ...)`: (str) Concatenation of `str1` and `str2`.
------------------------------------
**Bonus:**
- `instr(str, sub)`: (int) Position of `sub` in `str` (1-based index).
----------------------------------------
Please let me know if there is anything wrong. Thanks!
Hi i know its been long, i really hope i get a response, i have been following up to this point.
8:57 of this video, REPLACE String function, you used 'A' to be replaced with 'Z', but the name "Ann", the 'A' wasn't replaced. why?
Bro because he used lowercase letter 'a'
If you want to replace Ann
you have toh use 'A' upper case letter
Thank you so much ❤@@amanranjan9171
Very informative thanks Alex
Hmmm... Wonder how I managed to get my comment about the UNION lesson in the String Functions lesson???? Guess I should have been paying more attention to detail...! That said, this lesson on String Functions of succinct and clear. Everything works as advertised and it all makes sense. Nice job Alex!
9:17 with the "REPLACE" is there any way to recognize both lower and uppercase?
you could use a double replace to do that, like this:
select first_name, replace(replace(first_name, 'a','z'), 'A', 'Z' ) as
modified_name
from employee_demographics
;
think about it as you have your first replace based on first_name, then on the outer replace it takes the inner replace word which is only for lowercase a's and in those words it starts replacing the uppercase letter
Hi Alex, I want to start learning Data analysis and the laptop I chose to buy has a i5 13420h cpu (16gb ram, 1tb ssd) What do you think about the cpu? is it good enough for me to get started?
Hi Alex, i am having 10 years of experience as a Quality analyst in banking,can you please help me to suggest if self-study or doing a master's is a good option
Well, that was fun! Ending my day with Alex the Analyst while my lovely Bride is preparing dinner (I'm one lucky 'Wanna-Be-Data-Nerd'). Somehow UNION just makes more sense than JOINS for me. I just need more exposure and practice - like this Intermediate MySQL Series for example. Thanks again Alex! 😎
Am I getting it right that in order to extract the month from the date using SUBSRTING the date should be in a string format, not a date?
Yes, Substring doesn't work for Int datatype. You have to cast the type.
for example: SUBSTRING(Cast(birth_date AS varchar(8)), 6,2)
@@mdfuadhasancanada bro, I wish I could come here earlier. I searched for similar solution online multiple times but could not figure it out. thanks for sharing the solution.
@@miamdzobran Anytime bud!
Great stuff 😊✌️
What order by 2 means?
Please reply to this if you know what it means now, thanks
My 'Select' and 'From' are always lower case. Anyone else?
No issues as long as it doesn't give you a problem lol, as someone said, don't fix it if its not broken, but on other platforms, writing it in capitals help the most as much as I know
@@NohablaEspnaol heard
Thank you Sir❤
Is there a way to make the first letter of all names UPPER and the rest LOWER?
use substring for first letter with upper and for remaining letters ,use substring with lower and then concat both.
for example:Name :ALEX
select name,
concat(
(upper(substring(name,1,1)),
(lower(substring,(name,2,length(name)-1) ) #lower(substring(name,2)) you can also use this
) as new_name
from table1;
Can you show in mysql how to download csv files faster
Hi everyone,
I keep getting '10' as the length of the first name.
What could i be doing wrong?
I'm guessing you have quotes around first_name, so it's interpreting it as a string, not a column.
Hello how can I download this data??
Through the github link in the description of the first video of him teaching sql.
Interesting
Is it really intermediate?
Look's like
You and semicolons 😂