Advanced SQL Tutorial | String Functions + Use Cases

[@havinashz]

as a fellow office fan and an aspiring data analyst i love your content

[@JamesAndrewA]

I love this series please make more! I cant wait for your portfolio project.

[@AlexTheAnalyst]

Same! I only have one or two more videos and then onto the projects!

[@JamesAndrewA]

@@AlexTheAnalyst I want you to know how much I really appreciate your content. I don't want to sound like I am kissing your butt, but hear me out, I cannot gush enough about you. I think I am a bit ahead due to taking classes on the subject and using SQL, but your content is so clean and clear I just wanted you to know how great it is and how much you have helped me. I got a degree in electrical engineering, hated it, and couldn't find work other than a dream job I turned down because I am an idiot who didn't want to move. I said screw it, I can't find work, I might as well go back to school for a masters. "Well what the do I do now?" I liked MATLAB (which for anyone in EE reading this, no one remotely cares about or uses) So I picked Software Engineering. Then in a random KZbin search I found your videos while looking for help with homework. You showed some cool stuff and actually sat down with your subscribers live and talked about real word experience and gave advice on the field. Insane for today. I ended up taking a database management class and am currently in "advanced database systems" and love both of them. I love SQL and really hope after I finish school this year I can get a job working with databases. Right now I am looking at other tutorials online for using visual studio and learning integration and reporting services. This summer semester I am going to try to get an internship with the school working with databases and the following semester I will be taking a course on data warehousing and mining. Again, I hope to you and anyone reading this I don't sound over dramatic, but you and a rare unicorn of a professor in my college gave me some direction and I really appreciate it.

[@KritikaKabra]

@@JamesAndrewA totally agreed! What a gem 💎 I'm pursuing Bachelor's in Chemical Engineering (which is an interesting field but not so much for me) and I found Alex at such a good time. So grateful for his insights and guidance!

[@CE-vd2px]

@@JamesAndrewA Project series is now available

[@CareerHubSpot]

​@@KritikaKabra ❤

[@niroshamirthipati5833]

Thanks for creating this content, Alex. I request you to create a video on Ranking Window functions like Rank (), Dense_rank (), Row_Number () with the same database.

[@josephj1643]

This was exactly what I needed for one of my assignments in my course! Thanks a ton!

[@41dhir]

Apparently you and Michael both hate Toby a lot. First giving him 0.0001% raise and now this.

[@AlexTheAnalyst]

He deserves it just for being Toby

[@sonatakca5032]

@@AlexTheAnalyst💀

[@ifedijnrezenwobodo4119]

I don’t know if this helps, but I found out you can run TRIM, LTRIM, RTRIM, Lower and Upper, all in one go from Select. Example: SELECT EmployeeID, LTRIM(1002) As IDTRIM, LastName, RTRIM(‘John’) As Name, LastName, UPPER(‘Jim’) As New FROM EmployeeErrors

[@SamFisherman59]

loved the "Flenderson - Fired" ... good idea to keep the video informative and fun ... very clever.

[@plucks86]

Using The Office names was dope.

[@catarinamacedo2864]

I didn't see bad data at all hahahah It'd not be a surprise if Michael Scott put Fired as the last name of Toby and write it wrong, and put Beasely and Jimbo nicknames as well 😂😂Gotta love the consistency with the show in this tutorial

[@gwenchen1771]

Great video :)! Thanks so much! is it possible to do tutorials or a video with your opinions on while loops, cursors or dynamic sql? I'm wondering how useful they are/use cases for each and is it worth it to spend time studying them...

[@YouTubeVenJiX-zl4bj]

Hello sir can you please make one separate video on substring 🙂

[@anthonynkem]

Awesome tutorial. Thanks Alex!

[@AlexTheAnalyst]

Glad you've been enjoying the series! :D

[@StevenDonaldson11]

Whoa the sound is suddenly better.

[@islammounier1477]

this course is very useful and quick not boring at all 😍🤩🤩

[@digileapmarketingservices]

very well explained. Thank you for sharing contents like this

[@asiagladden3080]

This was so clever! Thank you!

[@1440MAP]

Learned. Thank you!

[@ArbHunter]

Thanks well explained Looking forward to your py videos, are you doing any pandas ones?

[@AlexTheAnalyst]

Oh yes - I plan on a whole series of just Pandas :)

[@user-ih7ot7vl8u]

Many thanks for great tutorial. 🙌🏻

[@blip_qild]

8:05 can someone tell me how he could just type [EmployeeDemographics] without adding the sources [SQLTutorial] like the previous Queries??

[@AbiCabadin]

He is on the SQLTutorial database. He does not need to specify the location of the table.

[@amaoamao9443]

Thank you! I did learn a lot!

[@s.g.937]

And if we wanted to lower case just the second letter of the name TOby for example, or any other name in which the second letter is uppercase we could use the following: *LEFT meaning only the letters starting from the LEFT, and the number 1 means only 1 letter. Basically only the first left letter. *LEN meaning the length of the name, so basically this will use the maximum length of the name. SELECT FirstName, UPPER(LEFT(FirstName,1)) + SUBSTRING(LOWER(FirstName),2,LEN(FirstName)) AS CorrectedName FROM SQLTutorial..EmployeeErrors But is there a better and shorter way ?

[@flygame2743]

Can you use the UPPER or LOWER case Stringfunction on only the First Letter for example?

[@AlexTheAnalyst]

I believe there is something called PROPER() - or something like that. I don't use it myself, but something to look into.

[@Babulal32218]

Length function can be used to understand change post RTRIM better

[@danielorji595]

Proper works fine too, just tried it

[@Ifeypeters]

Thank you so much Alex, you've been a great help to my Data analytics journey. I noticed the original inputs on the error table doesn't change after using the TRIM functions, how do we achieve this?

[@Venky_Himalay]

Hi sir we have PROPER function in EXCEL do we have anything like that in SQL

[@romanvasiura6705]

I am curious to know... Why Alex put comma(s) before line (Create/Insert statement)? Thank you for these tutorial videos 😀

[@chrisomoha636]

great lesson

[@katedavid4519]

Hi Alex, thank you for this tutorial. I am getting an error for the substring - Invalid object name ‘EmployeeDemographics’ Please how do I sort this? I’m stuck

[@al-kaviulsarker9733]

I did not understand the fuzz matching and why only TOby came as outcome?

[@paarthkohli6552]

I like those references to The Office 😂

[@FrederickFongTW]

More information on this.... need to watch more than once 😂

[@AhmedAli-pp1jo]

Great video) I got one question, how we can replace last name, here is the situation. I put where condition it did not work FirstName Last Name Alison -Plunket Madison -Plunket

[@allahverdibalamemmedov-jl4uv]

amazing tutorials

[@jonasvianney6371]

Hello Alex My name is John all the way from Uganda(africa) I wanted to follow a path to becoming a data analyst through self study but all I need from you is what exactly do I need to study n concentrate on. otherwise thanks for the good work

[@banku3708]

Can’t u use update instead of the replace . Because I used update and it got it done

[@uchindamiphiri1381]

I am really enjoying this. at first I tried all programming languages but now I think I know what I want. I want to be a data scientist and am sure taking a data analytics course to master these skills first isn't wrong right?

[@ritizadhikari7984]

same here for me. Tried going into cyber security but didn't find so much enthusiasm in that. Now here learning SQL and python libraries like numpy and pandas

[@pourannazariansamani1400]

Hi Alex! by running substring code , I didn't get the result in the table?? select err.FirstName, substring(err.FirstName, 1,3), dem.FirstName, substring(dem.FirstName, 1,3) From EmployeeErrors err join EmployeeDemographics dem on substring(err.FirstName, 1,3) = substring(dem.FirstName, 1,3)

[@jackp1490]

Should've made the error for Pam as 'Pan' "Okay, see you later Pan" - Dwight

[@AlexTheAnalyst]

Missed opportunity right there

[@Quis1989]

Can you join on multiple fields? In other words, have multiple ON statements in a single join?

[@markt8696]

hey man, do you any ENFPs who love being a Data analyst?

[@giuliamancini5887]

Hi Alex! Thank you for this amazing tutorial! I run the substring code that you have kindly shared with us and that i put below but it show me just Jim and Pam but not TOby. Maybe it's keysensitive or something else? I know that i gave you just few infromations and i don't expect an answer but i thank you for this beautiful video. I send you an hug! Select Substring(err.FirstName,1,3), Substring(dem.FirstName,1,3), Substring(err.LastName,1,3), Substring(dem.LastName,1,3) FROM EmployeeErrors err JOIN EmployeeDemographics dem on Substring(err.FirstName,1,3) = Substring(dem.FirstName,1,3) and Substring(err.LastName,1,3) = Substring(dem.LastName,1,3)

[@gagatonukari6482]

The same thing happened when I executed the substring code, were you able to find a solution?

[@jrgelpe8434]

I think it has something to do with the INNER JOIN OR JOIN in the query. Since Tob is different to TOb, it will not satisfy the matching.

[@TheMarioBrotherz]

I found the solution, Toby got deleted from the intermediate class UPDATING/DELETING SQL data. need to reenter his information

[@alaeddinmsetri4788]

Good expalanation thanks

[@charitaa1218]

What if we want to filter numbers instead of strings based on first 3 or last 3 values from a column,is there a specific alternative to substring function?

[@shokoofehheidari5345]

Great video :)! Thanks so much!

[@user-ui9ti3wk7n]

The fuzzy matching code does not work for TOby on my end, it only works for Jim and Pam

[@yanpaucon1043]

same, Have you figured it out already? Thanks

[@newbie6377]

why do i have two rows of TOby after executing ?

[@krishnabarfiwala5766]

Hey Alex, awesome video. Do you have a video for Windowing functions?

[@NB20079]

Hi, how to fix this. After creating a table, I can't see where the table is?Before I had the success of creating a table but now i can't see the table after creating the table, can anyone help me? Thank you

[@rahathussain9313]

how do i correct table.EmployeeErrors ID-1002 row of extra spacing through sql not manual replacing

[@manthansharma2578]

Hi Alex, Thanks for the boot camp, Really helpful for newbies. Just a question, Why can't we just use the update command instead of replace, Like: Update EmployeeErrors Set LastName = 'Flenderson' Where Firstname = 'Toby' The outcome is the same.

[@rebellis7904]

YOU CAN, IS JUST OTHER WAY TO GET THE SAME RESULT

[@willieleaverton668]

You explain everything so smoothly I appreciate your work! In the last part of this video, is there a way to only lowercase the "O" in "TOby"? Or is it all upper / all lower?

[@vrushalikulkarni4452]

Hi Willie. This will help SELECT error.FirstName, UPPER(SUBSTRING(error.FirstName, 1,1)) + LOWER(SUBSTRING(error.FirstName, 2, LEN(error.FirstName) - 1)) FROM EmployeeErrors as error

[@mayavik1034]

Did you mean like ‘Proper’ in Excel?

[@pensenaute]

@@vrushalikulkarni4452hi! Why isn’t it enough to just lower the “o” using your substring query?

[@anthonyd9043]

Thanks Alex, I'm curious if there's a TITLE string command to revert back to "Title" case from either "UPPER" or "lower", something I come across occasionally.

[@annepivovarov2484]

Hello, we have the same question. Mind sharing the answer if you found one already? Thanks!

[@avidhakal6451]

Hey alex thanks for the video Just a question Why do sometime i need to refer to whole database and sometimes just the table name while refering to the tables during join or some other operations ?

[@halildurmaz7827]

4:47 Here, "- Fired" should be " - Fired" Otherwise, you will return "Flenderson " (with 1 space character)

[@dlcrdz00]

I noticed that some commands were written in lowercase and some in uppercase; is there a best practice procedure using either format?

[@nehamehra3736]

@5:09 This query is not working in MySQL.

[@CaelestsFilius]

Why can't you do it as replace(lastname, '-%',' ') ? Is there a reason you can't use the wild card?

[@samuelraj5613]

Done

[@MrDannie7271]

thnak you for the git hub, can you add that to all your videos? thank you.

[@islammounier1477]

i just wanna ask there are upper and lower ..what about the proper is it exist or not ?

[@rafatruong]

Dear Alex, I can't use Trim function in SQL 2018. SQL show messege : 'TRIM' is not a recognized built-in function name. Tks for help Alex. :D

[@chefernandez563]

im a marketing major trying to learn data analytics, i do not have any background in programming so i dont understand the substring function at all I cant imagine a use for it other than getting a few characters from a first name for example. watched it over still dont get it😂

[@user-le2ei6ue7l]

Why use LTRIM and RTRIM at all? Why not just TRIM everything?

[@kobi981]

Hey Alex how are you? Hope you’re doing good I’m gonna start school in the next summer ,the studies that I want are statistics with Data science. I wanted to ask you 2 questions: 1. How can I know for sure that I like the data science studies? 2.unfortunately, the first course in the degree is Java and I heard that it’s very hard Compared to python or sql.. I’m new in the programming world and I want to start the basic python course soon..how can I prepare myself better for the degree and Java studies? Thanks and stay safe:)

[@AlexTheAnalyst]

Hey! 1. The best way to know is to try! You can always take some courses on Udemy beforehand and see if you enjoy those. 2. Yes, Java is complicated comparatively, but can be useful to know. I don't use it at all, but I've taken some courses on it. Not my thing to be honest lol My biggest advice is to start studying now so it isn't a total shock when you start.

[@kobi981]

@@AlexTheAnalyst thanks man!

[@jismissalim9214]

Hi Alex, I want to ask what's difference replace and update?

[@rohansrivastwa827]

How does your column name automatically change to its name in the column.. .I struggle a lot in writing exactly my table.column_name...pls explain to anyone!

[@emekahenry8140]

Using Replace: Flenderson-fired nothing Changed. Mine didn't work. Any help

[@emekahenry8140]

Solved it. I had to put space between ‘-' and fired for it to work. Great Tutorial Mr. Alex

[@sreeramvenkatasubramanian6300]

How it could be if I need to change the case of one specific character alone. -- Select FirstName , Lower(Substring(LastName, 1,1) from Table_Name; -- # I am changing the first letter of my last name into lower case. Do you think is this a right approach? if not, kindly rply with the correct method to do.

[@naorl8866]

How can you fix TOby by make only the first letter upper and the rest lower?

[@mercymallel2074]

Select FirstName, UPPER(SUBSTRING(FirstName,1,1)) + LOWER(SUBSTRING(FirstName,2,8)) FROM EmployeeErrors

[@yanpaucon1043]

It only showed jim and Pam, I don't know why it didn't show Toby.

[@hirdyeshvanvani5763]

Great Video, I was wondering if there is an efficient way to convert the string into Proper case? since SQL doesn't have any PROPER function..

[@AlexTheAnalyst]

Maybe using substring with upper/lower. Substring the first letter and make it Upper and substring the rest and make it lower? Haven't tested it out myself through lol just a thought

[@anjaliwadhwani5425]

@@AlexTheAnalyst select firstname, (upper(substring(firstname,1,1)))+(lower(substring(firstname,2,100))) as new_name from EmployeeErrors where FirstName='sam' I tried this and it is working😀

[@elizabethwillrealtor]

Hi Alex, thank you ones again for putting this together. I have a little challenge. The substring didn't fully work for me. The command was right. It displayed only the column but didn't display the datas. The other string worked just right. How do I fix this please?

[@horangheeeeee]

Hi! I hope you figured it out already. But if not, I hope I can help you with it. Maybe because you are missing some information on EmployeeDemographics table. In the previous tutorial, Alex gave an example wherein we replaced new information on the EmployeeDemographics table ( I can't fully remember which tutorial it was), so we deleted that Employee and replaced a new one. Therefore you need to add the information first on EmployeeDemographics table before joining it to a new one (EmployeeError table). This will be the code if you are missing that data. Insert into EmployeeDemographics Values ('1005', 'Toby', 'Flenderson', 35, 'Male') Then you can now join these two tables mentioned in this tutorial.

[@catemarble149]

@@horangheeeeee thank you for this!!

[@ancamihaila3986]

Hi everyone! I don't get it why TOby doesn't appear in my table when I do the Substring.

[@miulerbm]

Hi, I was having the same problem. I think it was caused by some previous part of the bootcamp where Alex deleted that row. Inserting it again worked for me INSERT INTO EmployeeDemographics VALUES (1005, 'Toby', 'Flenderson', 32, 'Male') After that, run again the substring part and it should work.

[@joshuasolano9631]

Is this for checking purpose or editing purpose?

[@shyamss2338]

Can we apply lower on a substring function? Like select firstname, lower(substring(firstname,2,3)) from tablename

[@nicolasm9408]

Sure, but that would only output the substring you mentioned. You could do a CONCAT(UPPER(SUBSTRING(first letter),LOWER(SUBSTRING(Rest of letters))

[@sakirinteser9536]

As a beginner, is it necessary to SQL projects to showcase my skill in SQL? If so, what kind of projects do you suggest for a beginner to do?

[@freakhead10]

No, it is not in my opinion. You need just take a course maybe and watch some KZbin videos. In the interview, they typically will have you do an example of a group by, join, or temp table. At least for the entry-level positions. They won't ask to see an SQL project. Although, they may ask about your past projects and it's challenges.

[@justmedidi]

learning this is great, but how can these changes/fixes be made permanent? when i go through the examples, the table with errors still contain the errors and the data doesn't update..

[@pensenaute]

Hi! I’m wondering the same, did you figure it out?

[@franzjosefbenjaminmacapaga278]

Hi Alex, under STRING FUNCTIONS + USE CASES video - I tried to execute the Fuzzy match example you ran, however it is not showing me any results at all - I tried to Drop the EmployeeErrors table and created it again but still nothing is showing up I can only see column headers but no 'Toby' data :(

[@franzjosefbenjaminmacapaga278]

update: I got it now, it is because earlier in the videos we removed Toby in the EmployeeDemographics table so there is no result when I do fuzzy match query, what I did is to enter back toby's details in EmployeeDemo

[@adhiganesh9145]

Hi Alex, Nice video. I am currently doing the job role of Rpa Developer, specialized in Uipath. But I do not have a future as I am not an expert in Coding. Is it advisable for me to switch my career to Data Analyst? Please reply.

[@AlexTheAnalyst]

Hard to say without more information, but if you like databases and visualizations could be a good move?

[@rajkumarrajan8059]

Hi Alex, How can we do a proper case in SQL? Is there a way to do that?

[@rajkumarrajan8059]

Like in the above example 'TOby' to 'Toby'

[@marcelopereira1183]

Hi Alex, I'm a finalist on a bachelor and I just got an academic internship for a BI team.. I want to apply for my masters (at night) and although different I love both BI and Data Science... In terms of future, what would give me more job stability? Any thoughts? Thx in advance and thx A LOT for all the videos you make they really help!!!

[@YoSoyWerlix]

Which one did you chose?🎉

[@uchindamiphiri1381]

😀😆@@YoSoyWerlix

[@timemage729]

I seem to be having trouble with the REPLACE function. I have the query written out exactly as it is shown on screen, however, the last name is not being changed in the new column. When I tried to change the last option to change the last name, the same thing happened and nothing new was inputted into the new column. Any advice?

[@timemage729]

... never mind.. I was missing a space between the "- Fired" ..

[@joytotha]

😂😂Not Michael learning SQL to insert 'Fired' CREATE TABLE #Damnit_Michael (EmployeeID varchar(50), FirstName varchar(50), LastName varchar(50), Age int, Gender varchar(50)) INSERT INTO #Damnit_Michael SELECT * FROM EmployeeDemographics UPDATE #Damnit_Michael SET LastName = LastName + ' - Fired' WHERE EmployeeID = 1005 SELECT * FROM #Damnit_Michael

[@sriramvoruganti1399]

What's the difference between using the master server and the SQL Tutorial ? l am often confused which one to use usually

[@AlexTheAnalyst]

Just different databases. Use whichever one you put your table in.

[@swetachaudhary6826]

Its not working for me

[@pavanreddy9543]

Hi Alex, I have a question here about using UPPER and lower. How do we make the first letter as UPPER and all other letters following it as lower letters??

[@jwatts24]

It would be something as follows: UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))) To break it down - the first section is taking the starting on the left side of the FIrstName and only uppercasing the first letter of the name. The second section lowercases the remaining characters that are in the FirstName place. Using the substring function, you are gathering all the characters that are in the FirstName that starts at the second character. From that, you use the LOWER function to make sure everything that was captured in the substring function to be lowercased. I use this frequently for work. I'm sure there is a better way to do this but this is just memory at this point.

[@mayavik1034]

Did you mean like ‘Proper ‘ in Excel?

[@salehmehdizade]

But is this trim and replace stays in the table permanently ?

[@AlexTheAnalyst]

If it's in the Select portion, then no. You'd have to update the columns/table itself to permanently update it.

[@EMO_85]

Hello Alex, I have a little issue here. While inserting values to my EmployeeError table, the blank spaces inserted in the EmployeeID did not reflect on the table. My table executed without any blank space in the EmployeeID. What could I be doing wrong?

[@ignaciorepetto1061]

Hello! The same happened to me, probably caused by the data type. see that he has varchar(50) for the EmployeeID.

[@lorainemukwauri9349]

Hey Alex,great fan of your work by the way, but the SUBSTRING concept was poorly explained.

[@silencer91]

Hey Alex, I'm a cybersecurity major who has been exposed to python, java, HTML, and some networking as well. Does a sports analyst fit my major?

[@mayavik1034]

Did you do the Sports Analytics course on Coursera?

[@jakeduddy3257]

No regular expressions :(

[@AlexTheAnalyst]

Coming soon to a YT video near you :)

[@PlayerOne-GT]

👍

[@faizlabunu4460]

again this toby caught me offguard, finally toby got fired after didnt get raise salary and got deleted from list XD

[@shadan5487]

1:16 NO GOD! NO GOD! PLEASE NO!!! NO NO NOOOOOOOOOO

[@tyvanra2266]

CREATE TABLE dd/mm/yyyy ? Teach immediately thank you

[@antoniolupen6138]

But in reallity you aren´t correcting anything since the SELECT statement jost present a view of the table but altering nothing

[@rohansrivastwa827]

How does your column name automatically change to its name in the column.. .I struggle a lot in writing exactly my table.column_name...pls explain to anyone!

[@shyamss2338]

Can we apply lower on a substring function? Like select firstname, lower(substring(firstname,2,3)) from tablename

[@romanvasiura6705]

you can try)