Sort concatenated values.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sort concatenated values..

Author

Topic

acpt
Starting Member

4 Posts

Posted - 2008-11-04 : 13:27:13

Hi,

i have a string like 'd','a',c' from a query resultset.
Now, i need to sort this string and again get a concatenated output like 'a','c','d' . How do i do it ?

i thought of passing the original value to a stored procedure or a function.. the stored proc/func will split & store the value in a temp table such that each value is a new row..

TmpSort
--------------
d
a
c

I will then query this temp table and get the sorted values..

The problem i am facing is that if i use a function, i cannot do any t-sql statements within a function and if i use a stored procedure, i cannot call it inline..
Then i thought, i will create both.. the stored procedure will do the sorting and storing the sorted value in the temp table and i will call the stored procedure within the function and make it inline.. (ofcourse there will be 2 parameters... (i) the string and (ii) the delimiter)..
but alas, this too does not work.. i cannot call a stored procedure from within a function..:-(

Could anyone suggest a workaround to achieve this ???

Thanks..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 13:31:37

Post real sample data and expected output.

E 12°55'05.63"
N 56°04'39.26"

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-04 : 13:43:57

you can have all the t-sql statements inside a function, why do you need a sp inside a function?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-04 : 14:56:26

Here's an example of splitting the values, then reassembling.


declare @MYVal varchar(1000),@Loc int, @TmpValue varchar(20)
DECLARE @t TABLE (col VARCHAR(10))


set @MyVal = 'aii,j,b,k,c,d,m,daade,f,l,g,h,i'

set @Loc = 0

While (select charindex(',',@myval,@loc + 1)) > 0
begin
	if Charindex(',',@MyVal, @loc+ 1)  = Charindex(',',@MyVal, @loc - 1) 
		begin
		set @TmpValue = 	Substring(@Myval,0,Charindex(',',@Myval,@Loc))
		end
	ELSE 
		begin
		set @TmpValue = 	SubString(@myVal,@Loc + 1, Charindex(',',@Myval,@Loc + 1) -  Charindex(',',@Myval,@Loc) - 1)
		end

insert into @t(Col) values (@TmpValue)
set @Loc = CharIndex(',',@MyVal,@Loc + 1)
end

SELECT DISTINCT	
		STUFF((SELECT ',' + s2.Col FROM @t  AS s2 order by s2.col FOR XML PATH('') ), 1, 1, '') AS col

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-04 : 16:51:39

here's the code if you want to include the qualifiers


declare @MYVal varchar(1000),@Loc int, @TmpValue varchar(20)
DECLARE @t TABLE (col VARCHAR(10))


set @MyVal = '''aii'',''j'',''b'',''k'',''c'',''d'',''m'',''daade'',''f'',''l'',''g'',''h'',''i'''
select @Myval
set @Loc = 0

While (select charindex(',',@myval,@loc + 1)) > 0
begin
	if Charindex(',',@MyVal, @loc+ 1)  = Charindex(',',@MyVal, @loc - 1) 
		begin
		set @TmpValue = 	Substring(@Myval,0,Charindex(',',@Myval,@Loc))
		end
	ELSE 
		begin
		set @TmpValue = 	SubString(@myVal,@Loc + 1, Charindex(',',@Myval,@Loc + 1) -  Charindex(',',@Myval,@Loc) - 1)
		end

insert into @t(Col) values (replace(@TmpValue,'''',''))
set @Loc = CharIndex(',',@MyVal,@Loc + 1)
end

SELECT 	substring(STUFF((SELECT ''',''' + s2.Col FROM @t  AS s2 order by s2.col FOR XML PATH('') ), 1, 1, ''),2,10000) + '''' AS col

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources