Click here to Skip to main content
14,639,700 members
Rate this:
Please Sign up or sign in to vote.
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:-
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
Comments
   
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 :)
Rate this:
Please Sign up or sign in to vote.

Solution 1

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

Good luck,
OI
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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:

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:

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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100