Click here to Skip to main content
11,479,170 members (64,992 online)
Click here to Skip to main content

TSQL split a string by delimiter

, 13 Apr 2013 CPOL 23.4K 6
Rate this:
Please Sign up or sign in to vote.
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

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 !!

– 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shahriar Iqbal Chowdhury/Galib
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150520.1 | Last Updated 13 Apr 2013
Article Copyright 2013 by Shahriar Iqbal Chowdhury/Galib
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid