SQL Server Queries Part 8 - Text Calculations

By Andrew Gould

You can see a written explanation of some of the functions used in the video here:

You can download the script to create the Movies database and People database used in this video at the following link:

You can see the range of resources and courses we offer in SQL Server here:

[chunfungmak]

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

[azadehk]

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

[monamonik]

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.

[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!

[mrnobody]

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

[johnparlier]

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

[bhavyaramgiri]

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

[job]

Very informative and easily understood. Thank you!

[sukumarsa]

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

[charlyclerfeuille]

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.

[kristian]

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

[krismaly]

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

[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)

[johanhernansanchezvillano]

Que excellente video .

helped me a lot, thank you very much

[DarthKamci]

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

[MtchBXr]

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

[kylerhunter]

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?

[christphrson]

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