Split a string using a table Variable

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
FROM @strInput

The result as below:

Simple to begin with and there are a number things you can achieve using this base logic.

This entry was posted in SQL Server, T-SQL Scripts, Tricks and tagged , , . Bookmark the permalink.

One Response to Split a string using a table Variable

  1. Pingback: Energy Star LED Light

Leave a Reply