un split in sql

Question

Post reply

un split in sql

elham_azizi_62

SSCertifiable

Points: 5095
More actions
October 7, 2012 at 4:03 am

#282706

hello all.
i use this function for split:
ALTER FUNCTION dbo.GLB_Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (id int identity(1,1),items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
but i want to write function split inverse and no with cursor,please help me.
my target is:
input of function :tables of string
output of function:string that seprate with comma like this:a,b,c,d
please help me how do i do and i donot want cursor.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you require ?

DECLARE @String VARCHAR(8000),@Delimiter CHAR(1)

SET @Delimiter = '-'

SET @String = 'A-ab-xx-y-vv-w w-'

select @String, REPLACE(@String,@Delimiter,',')

Result:

(Orinial) (Replaced)

A-ab-xx-y-vv-w w- A,ab,xx,y,vv,w w,

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Take a look at the last option here: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

elham_azizi_62 (10/7/2012)
but i want to write function split inverse and no with cursor,please help me.

I'm a bit confused here. You use a split function that is a slow scalar UDF that uses a While Loop (the guts of a cursor). My recommendation would be to fix that bad boy first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

justmohit SSC Veteran Points: 280 More actions · Answer 4

You can use XML PATH for the inverse function in which input is table of string and output is comma seperated value.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

justmohit (10/7/2012)
You can use XML PATH for the inverse function in which input is table of string and output is comma seperated value.

That's real nice. Do you have a code example or a link or something?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

@elham,

The following link has some excellent examples of how to do as you ask and it explains the "why".

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 7

I want to have this:

select column1 from table1

column1

a

b

c

and result:a,b,c

and i donot want to use cursor or virtual table.please help me.thanks

yuvipoy SSChampion Points: 10469 More actions · Answer 8

declare @Temp Table(Col int)

Insert into @Temp

Select 1

UNION Select 2

UNION Select 3

UNION Select 4

UNION Select 5

Select * from @Temp

Declare @coalesce varchar(200)

Set @coalesce=''

Select @coalesce=@COALESCE+Convert(varchar,Col)+',' from @Temp

Select @coalesce=left(@COALESCE,LEN(@COALESCE)-1)

Select @coalesce

Thanks!

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 9

elham_azizi_62

SSCertifiable

Points: 5095

October 8, 2012 at 1:04 am

#1546575

thanks alot from you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

elham_azizi_62 (10/7/2012)
I want to have this:
select column1 from table1
column1
a
b
c
and result:a,b,c
and i donot want to use cursor or virtual table.please help me.thanks

GilaMonster (10/7/2012)
Take a look at the last option here: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass