'Extracting the first name, middle initial, and last name from a Full name column

I am learning SQL and I'm trying to extract the First Name, Last Name, and Middle Initial (if there is any) from a Full Name column. I was able do do this with the following query, but I would like to simplify the query by using variables.

What is the way to use variables to simplify the below?

SELECT DISTINCT trim(PROV_NAME) as FullName,
    CHARINDEX(' ', trim(prov_name)) as  FirstSpaceIndex,
    LEN(trim(prov_name)) as FullNameLength,
    SUBSTRING(trim(PROV_NAME), 1, CHARINDEX(' ', trim(prov_name))) AS FirstNameExtracted,
    RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name))) as RestOfName,
    LEN(RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name)))) AS RestOfNameLength,
    CHARINDEX(' ', RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name)))) as FirstSpaceInRestOfName,
    LEFT(RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name))), CHARINDEX(' ', RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name))))) as MiddleInitialExtracted,
    RIGHT(RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name))), LEN(RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name)))) - CHARINDEX(' ', RIGHT(trim(prov_name), LEN(trim(prov_name)) - CHARINDEX(' ', trim(prov_name))))) as LastNameExtracted
FROM ARCHIVE
sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions