Tag: column name

TSQL split a string by delimiter

Background

Say we have a registration table which store successful registration info. At that table we have a column “Name” which stores value as 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

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

objects-substring

— 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 this solution is only applicable if you have a string with a single delimiter and resultant split count is <=2. Though is small and wont work for more then one delimiter but its quite handy for situation like I explained above.