TSQL Split a String by Delimiter

Background

Say we have a registration table which stores successful registration information. At that table, we have a column “Name” which stores value as a combination of first name & last name delimited by ‘ ‘ [space]. Later at some point, we need to extract the first name or last name from that column, what to do?

Solution

The solution is a simple technique that will take the given input string (or column in my scenario) and split against the given delimiter. The technique is to get the first index of delimiter and return the string up to first index and return the first half. For the second half, start from first index of delimiter and take till the end of input string and you will get the second half. Simple, isn't it !!

• Split and get the first half, delimiter is ‘ ‘ [space]

  LTRIM(SUBSTRING([COLUMN_NAME], CHARINDEX( ‘ ‘,[COLUMN_NAME])+1,len([COLUMN_NAME])))

• Split and get the second half, delimiter is ‘ ‘ [space]

  LTRIM(SUBSTRING([COLUMN_NAME], 0,CHARINDEX(‘ ‘,[COLUMN_NAME])+1))

And let me remind you that this solution is only applicable if you have a string with a single delimiter and resultant split count is <=2. Though this is small and wont work for more then one delimiter, it's quite handy for a situation like I explained above.