FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

create table Names
(
	Name varchar(100)
)

insert into Names values
('Smith Johns Sr'),
('James Macoy'),
('Krushit J Patel II'),
('Sheldon Devid'),
('Jeff vandorf Jr'),
('Steve Smith I')

SELECT      
 SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1) AS FirstName
,CASE LEN(Name) - LEN(REPLACE(Name, ' ', '')) + 1
    WHEN 2 THEN NULL
    WHEN 3 THEN NULL
    WHEN 4 THEN SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))) - 1)
 END AS [Middle Name]
,CASE LEN(Name) - LEN(REPLACE(Name, ' ', '')) + 1
    WHEN 2 THEN RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))
    WHEN 3 THEN SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))) - 1)
    WHEN 4 THEN SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1))) - 1)
 END AS lastName
,CASE LEN(Name) - LEN(REPLACE(Name, ' ', '')) + 1
    WHEN 2 THEN NULL
    WHEN 3 THEN REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name)) - 1))
    WHEN 4 THEN REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name)) - 1))
 END AS Suffix
FROM Names

FirstName	Middle Name	lastName	Suffix
Smith	NULL	Johns	Sr
James	NULL	Macoy	NULL
Krushit	J	Patel	II
Sheldon	NULL	Devid	NULL
Jeff	NULL	vandorf	Jr
Steve	NULL	Smith	I

(6 rows affected)

(6 rows affected)