Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

un split in sql Expand / Collapse
Author
Message
Posted Sunday, October 7, 2012 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
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.
Post #1369515
Posted Sunday, October 7, 2012 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #1369521
Posted Sunday, October 7, 2012 6:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1369522
Posted Sunday, October 7, 2012 8:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369534
Posted Sunday, October 7, 2012 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:57 AM
Points: 20, Visits: 78
You can use XML PATH for the inverse function in which input is table of string and output is comma seperated value.



Post #1369558
Posted Sunday, October 7, 2012 6:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369584
Posted Sunday, October 7, 2012 6:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369585
Posted Sunday, October 7, 2012 10:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
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
Post #1369612
Posted Sunday, October 7, 2012 11:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!
Post #1369620
Posted Monday, October 8, 2012 1:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
thanks alot from you.
Post #1369636
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse