SQL Server Queries Part 8 - Text Calculations

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
By Andrew Gould
www.wiseowl.co.uk - Learn how to perform calculations with text in Microsoft SQL Server queries. This video teaches you how to create calculated columns in a query involving text, including how to concatenate separate pieces of text into longer strings and how to use functions to split a longer piece of text into separate parts.
You can see a written explanation of some of the functions used in the video here:
www.wiseowl.co...
You can download the script to create the Movies database and People database used in this video at the following link:
www.wiseowl.co...
You can see the range of resources and courses we offer in SQL Server here:
www.wiseowl.co...
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!

[@chunfungmak4651]

I am from Hong Kong, thanks for updating these good works, which now help me survive in the workforce containing SQL database!

[@bhavyaramgiri9396]

LEFT( FullName , ABS(CHARINDEX( ' ', FullName) - 1)) wrap charindex with absolute in-case anyone getting error @ 8:30 .

[@WiseOwlTutorials]

Thanks Bhavya!

[@azadehk3010]

It was perfect and useful for me. I am watching all your videos about SQL now.Thanks

[@BillKennedyVideo]

Learned a lot from this video. It has inspired me to take it one step further to adapt to the variations on how the actor's names are formatted. The code: ,RIGHT(ActorName,(LEN(ActorName)-CHARINDEX(' ',ActorName))) works great for normal firstname/lastname combinations, but it is also giving me output like this: L. Jackson Bob Thornton Lee Jones So far I haven't been able to figure this out. I sure would appreciate another video on how this could be done. Thanks!

[@mona0monik]

regarding your comment that it's a case of preference if you use CAST or CONVERT, I would have liked if you mentioned that CAST is ANSI, while CONVERT is SQL Server specific but can allow specific date formating. My opinion is that CAST should always be used (for portability) while CONVERT - only if you need the date formatting option.

[@johnparlier1326]

awesome videos!! Will be watching the rest and I'm sure re-watching.

[@job187]

Very informative and easily understood. Thank you!

[@justinbanza4751]

Thank you very much for this video

[@WiseOwlTutorials]

You're welcome Justin, thanks for watching!

[@syednoumannaseer4768]

Awesome work man keep it up!

[@divyal5852]

Thank you, Andrew!

[@mrnobody6743]

For the FirstName LastName breakdown of FullName, if you have single names such as "Sting", "Flea", "Jewel", you will need additional code to extract the first/last names without errors or duplication. Note: The below code doesn't break out middle names or name suffixes. I am not sure if this is an efficient method, just something I was playing with. USE Movies SELECT FullName --,LEFT(FullName,CHARINDEX(' ',FullName)-1) AS 'FirstName' ,LEFT(FullName, CASE WHEN CHARINDEX(' ',FullName)-1 < 0 THEN LEN(FullName) ELSE CHARINDEX(' ',FullName)-1 END) AS 'FirstName' --,RIGHT(FullName,LEN(FullName)-CHARINDEX(' ',FullName)) AS 'LastName' ,RIGHT(FullName, CASE WHEN (LEN(FullName)-CHARINDEX(' ',FullName)) = LEN(FullName) THEN 0 ELSE LEN(FullName)-CHARINDEX(' ',FullName) End) AS 'LastName' FROM Actor

[@sukumarsa9039]

Great effort, but at timeline 7:58 , should it be that basically tells me how many letters there are in First Name?

[@krismaly]

I enjoyed watching this video and it was useful. What is the difference between & and + signs?

[@charlyclerfeuille4212]

Thanks again. Trying this 11 years after its publication, I am astonished by how antiquated SSMS can be as for its interface : -The way to find functions, needing n clicks is unchanged. -Once a function is started, the tooltip - so useful in Excel - appears once and that is it. -As for using F1 / web documentation : why not. But try this while selecting the function Substring (I guess the 2nd one you were to mention to manage the Last Name extraction) and you will get to the CHARINDEX page because... reasons ? MS Office or Windows has had its fair share of missteps (Bring back the Do not combine !) regarding UI/UX but SSMS seems stuck in the 90's and VBA does not look so bad suddenly. Weird.

[@WiseOwlTutorials]

Agreed! Have you tried Azure Data Studio? It's a more up-to-date application with some nice basic visualisation features when writing queries. It doesn't have all the administrative features of SSMS but you might prefer it if you're just writing queries. It should be installed automatically with recent releases of SSMS but just in case: azure.microsoft.com/en-gb/products/data-studio

[@charlyclerfeuille4212]

@@WiseOwlTutorials Thanks for the link, I will definitively try it later. For now I will go on with what I have. I need to code hundreds of queries to actually digest the entire series/possibilities before looking for optimization.

[@kristian3401]

Where did the People table come from? I only have movies from your downloadable file...

[@johanhernansanchezvillano5212]

Que excellente video . helped me a lot, thank you very much

[@M4tchB0X3r]

well made tutorials, also you sound like Richard Dawkings!!

[@DanKardell]

Great job again! Hey is there any "right way" to handle names like Samuel Jackson that has a middle initial in it? (I know the right answer is to do it in code and outside the query but just wondering if there was a way)

[@DarthKamci0]

What would happen if in one row there was no spaces at all? How would the CHARINDEX and LEFT functions behave?

[@gabbyess]

why not to use concat function?

[@kylerhunter7976]

Question - what if you have columns that you've created before hand that you would like the data to go in? So your have the column ActorName and you want the data to go into ActorFirstName and ActorLastName. How would you modify your query to do that? I'm a novice on this and need a little extra help. Thanks

[@theoldhopbarn]

Is there any reason why I shouldn't use ... + STR(FilmOscarWins,2) instead of the CAST or CONVERT functions?

[@christ0ph3rson70]

Where can I download this sql workbench so I can practice?

[@wahahah]

Wait, for the the Right command and the LENS, how do you know when you subtract the CHARINDEX it doesn't subtract from the wrong side? Like what if it subtract the right side. How would uk lol

[@kameronrogers4000]

CHARINDEX gets the index of the first appearance of the character you are looking for going from left to right. That is why, "Sacha Baron Cohen" turns into "Baron Cohen, Sacha"