Convert String and Sort the the Intergers in a String ASC

[vuyiswam]

Good Day All

i have the Following Example String

10,11,9


This is a Varchar Field in SQl, and i want this Sort the numbers inside this String to be like this

9,10,11


Thanks

[Alsvha]

If it is one field which contains it I would not do it in SQL syntax, but look at doing it in a proper programming language.

But if I were to do it in SQL, I'd properly pull the information out into a (temporary) table. Because then you can re-create the string by selecting from said table and then concatenate the result into a new string.

It is also possible that some FOR XML syntax might help you out, but I'm not terrible into the syntax to help with that.

[Alsvha]

Actually - felt it was fun enough, so decided to try and make an XML example which would do it:


declare @x xml
set @x = ''

declare @str varchar(255)

set @str = cast(@x.query('for $i in (11, 9, 10)
order by $i
return fn:concat(xs:string($i), ",")') as varchar(255))

select left(@str, len(@str) -1)


I don't know how effective it is, but it returns '9, 10, 11', so check it out - it might work for you. Or lead you in a path for which you can solve your problem.

[George1111]

The way I have dealt with this before is use the VAL qualifier

Assuming you are sorting on a Text Field - call it MyTextField

If you say

"Select .............. Order By MyTextfield"

then 9,10,11 will sort as 10,11,9 (Alphabetic Order)

But if you say

Select ................ Order By Val(MyTextField)

then you should get 9,10,11 (Numeric Value Order)

[Alsvha]

Just wanted to mention that VAL is also database specific, so the syntax might not work if not using an engine supporting it.
Doesn't exists in MS SQL at least from what I can see.

(also forgot to note that my own example was in MS SQL 2005/2008)

[vuyiswam]

Thanks for your Help.

I found the Solution

Thanks