how to write a function to separate or split function to separate comma separated value in this table?

Question

how to write a function to separate or split function to separate comma separated value in this table?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
August 3, 2012 at 1:20 am

#259605

here i am having table
declare @test1 table(
id int,
telephone varchar(4000)
)
insert into @test1
select 1,'004-26487,004-84576,9845678682' union all
select 2,'99942078682,002-2687,002-84576,99945678682'
select * from @test1
which provide a out put like this
idtelephone
1004-26487,004-84576,9845678682
2002-2687,002-84576,9845678682
now i just want split this telepone no like thistelepone no seperately and mobile no seperately
when data ' -' below 10 it has to be placed in telephone
idtelephone mobile
1004-26487 9845678682
1004-84576 9845678692
2002-2687 9845678682
2002-84576 9845678682

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

Jeff Morden's 8K splitter may be able to help and it's published right here on SQLServerCentral.com ;I used for a similar task sometime ago and it performed perfectly.

See http://www.sqlservercentral.com/articles/Tally+Table/72993/

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 2

Why does 99945678682 not appear in the results?

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

No functions...

-- sample data

declare @test1 table(

id int,

telephone varchar(4000)

)

insert into @test1

select 1,'004-26487,004-84576,9845678682' union all

select 2,'99942078682,002-2687,002-84576,99945678682' UNION ALL

SELECT 3,'002-2687' UNION ALL

SELECT 4,'' UNION ALL

SELECT 5, NULL

-- solution

SELECT

id,

NumberList = s.telephone,

telephone = CASE WHEN CAST(LEFT(cav.t,3) AS INT) < 10 THEN cav.t ELSE NULL END,

mobile = CASE WHEN CAST(LEFT(cav.t,3) AS INT) >= 10 THEN cav.t ELSE NULL END

FROM @test1 s

-- cascaded (CROSS)APPLY

CROSS APPLY (SELECT pos = ISNULL(NULLIF(1+CHARINDEX(',',telephone,1),1),2+LEN(telephone))) x1

CROSS APPLY (SELECT pos = ISNULL(NULLIF(1+CHARINDEX(',',telephone,x1.pos),1),2+LEN(telephone))) x2

CROSS APPLY (SELECT pos = ISNULL(NULLIF(1+CHARINDEX(',',telephone,x2.pos),1),2+LEN(telephone))) x3

CROSS APPLY (SELECT pos = ISNULL(NULLIF(1+CHARINDEX(',',telephone,x3.pos),1),2+LEN(telephone))) x4

CROSS APPLY (SELECT pos = ISNULL(NULLIF(1+CHARINDEX(',',telephone,x4.pos),1),2+LEN(telephone))) x5

CROSS APPLY (SELECT pos = ISNULL(NULLIF(1+CHARINDEX(',',telephone,x5.pos),1),2+LEN(telephone))) x6

CROSS APPLY (

-- CROSS APPLY VALUES see http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

VALUES

(LEFT(telephone,x1.pos-2)),

(SUBSTRING(telephone,x1.pos,NULLIF(x2.pos-x1.pos,0)-1)),

(SUBSTRING(telephone,x2.pos,NULLIF(x3.pos-x2.pos,0)-1)),

(SUBSTRING(telephone,x3.pos,NULLIF(x4.pos-x3.pos,0)-1)),

(SUBSTRING(telephone,x4.pos,NULLIF(x5.pos-x4.pos,0)-1)),

(SUBSTRING(telephone,x5.pos,NULLIF(x6.pos-x5.pos,0)-1))

) cav (t)

WHERE s.telephone IS NULL OR cav.t IS NOT NULL

EDIT: tweak to first element and WHERE clause.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 4

hi havindg this problem when i execute this query