Was just playing around with Table variables and thought of a practical problem lot of people approached to me with. Thought its worth documenting on how I do it so somebody can reuse the trick.
Given a set of names you want to split the First name and Last Name identifying the first Space as delimiter between the two parts of the name.
The way I do it to use a table variable to store all the names to process
and then use a set of string functions to calculate the charindex and select substrings based on the requirement as below.
DECLARE @strInput TABLE ( Name VARCHAR(256) )
INSERT INTO @strInput
( Name )
VALUES ( ‘Abc 123’ ),
( ‘Ade Abc’ ),
( ‘Xyz Abc’ ),
( ‘123 Abc’ ),
( ‘123 xyz abc’ )
SELECT LEFT(Name, CHARINDEX(‘ ‘, Name) – 1) AS FristName ,
SUBSTRING(Name, CHARINDEX(‘ ‘, Name) + 1,
DATALENGTH(Name) – CHARINDEX(‘ ‘, Name)) AS LastName
The result as below:
Simple to begin with and there are a number things you can achieve using this base logic.