How to split one column value int to the three columns?

Question

3.00/5 (2 votes)

See more:

SQL-Server-2008

Hello,

I have one table and a column, i want to split one column value into three column (comma separated value)
I have a table data.
insert into adddetails values('abc,123213,Delhi')
I am writing a query for split values but how to break into three column.
my query is:-

SQL

select SUBSTRING(address,1,CHARINDEX(',',address)-1) as Address1,
SUBSTRING(address,CHARINDEX(',',address)+1,LEN(address)) as Address2
from adddetails

It's query of two column but i need three columns like

Address1 Address2 Address3
abc 123213 Delhi

Please help me.

Ankit Agarwal
Software Engineer

Posted 14-Jan-13 19:37pm

ankit.agg1984

Add a Solution

Comments

Sergey Alexandrovich Kryukov 15-Jan-13 1:43am

Bad idea. Why would you need it. Anyway, the idea of "comma-separated" has nothing to do with SQL.
—SA

Joezer BH 15-Jan-13 6:03am

Hi Sergey,

You are absolutely right about the comma being a bad idea to use in the DB
But - if you have to deal with some other guy's DB and you do have to split the cell into 3 fields, that is a nice question. and the answer is to use CHARINDEX twice, once to find the first appearance and the second to start searching after that index in order to find the second.

Sergey Alexandrovich Kryukov 15-Jan-13 10:44am

Understood, thanks.
—SA

Joezer BH 15-Jan-13 4:52am

Nice Question :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Orcun Iyigun** · Answer 1 · 2013-01-14T20:10:00

Solution 1

Easy!![^] Google is your friend use him whenever you are stuck!

Good luck,
OI

Posted 14-Jan-13 20:10pm

Orcun Iyigun

**Joezer BH** · Answer 2 · 2013-01-14T22:50:00

Hello Ankit,

Quite simple :)

You've managed to find the first comma by the CHARINDEX,
and your problem is to find the others (in your case the second).

What you can do is start the search for a starting index, after the first appearance.

See this section of Vishal's post on sqlandme:

SQL

DECLARE @string VARCHAR(128)
SET @string =     'CHARINDEX searches for the string for the ' +
                  'first occurrence of a specified character/string'
SELECT      CHARINDEX('Char', @string)    AS 'Position',
            CHARINDEX('Char', @string, 2) AS 'Position'

Result Set:

Position Position
———– ———–
1 - 75

(1 row(s) affected)

In the above example, the first columns returns 1 as the string started with 'CHAR…', while in the second column the search was started from 'HARINDEX…', which resulted in returning the second occurrence of 'CHAR'.

Searches are based on the collation of the data. CHARINDEX() has performed a case-insensitive search as my current collation is set to Latin1_General_CI_AI. To perform a case-sensitive search here we can apply Latin1_General_CS_AI collation to @string:

SQL

DECLARE @string VARCHAR(128)
SET @string =     'CHARINDEX searches for the string for the ' +
                  'first occurrence of a specified character/string'
SELECT      CHARINDEX('char', @string COLLATE Latin1_General_CS_AI)
            AS 'Position'

Result Set:

Position
———–
75