Sort the values in the column in ascending order

[jonamg]

Guys

I have a scenario where a column contains values like '1 5 2 6', '2 4 1 5 7', etc. The length of the column varies.
I want the output to be '1256', 12457' for the above values which means to trim the data and sort it.

Any suggestions how to implement it?

[SURA]

You mean to say the value is Integer for all the records?

DS User

[jonamg]

its varchar(7)

[SURA]

I mean to ask, the values for vc(7) is numbers or ...?

DS User

[jonamg]

yes.It contains only inter values.

[SURA]

You can use convert function get this.

Trim(Convert(Convert("0123456789","",In.Col),"",In.Col))

DS User

[chulett]

No... note that the individual values within the string are being re-ordered ascending. There's no simple way to do that. Can't even think of a complicated way right now.

[SURA]

You are right. My mistake; i havent read the query properly.

Sorry

DS User

[jonamg]

Update : the column also contains characters as well

[ray.wurlod]

You are going to need a routine to sort the characters in a string.

[qt_ky]

Update : the column also contains characters as well

The example you mentioned is larger than what will fit in varchar(7): '2 4 1 5 7'.

Do you need to sort in ASCII order or some other order?

Could you have an input like '8 e E % 10' with mixed case, symbols, two-digit numbers, etc.? What should the sorted output be?

That could make a big difference on how to handle sorting.

[jonamg]

Here are distinct values that are in the field. I dont think we have combination of letters and numbers.

0
010160
1
1 2
1 2 3
1 2 3 5
1 2 3 7
1 2 5
1 2 5 6
1 2 5 7
1 2 7
1 3
1 3 5
1 3 5 6
1 3 5 7
1 3 6 7
1 3 7
1 5
1 5 6
1 5 7
1 6
1 7
12
123
12357
125
1257
127
13
135
13567
15
157
17
2
2 3
2 3 5
2 3 5 7
2 3 7
2 5
2 5 7
2 6
2 7
23
2357
26
3
3 5
3 5 6
3 5 6 7
3 5 7
3 6
3 7
35
4
5
5 6
5 6 7
5 7
51
56
57
6
7
C
J
Q

[qt_ky]

Those mostly appear to be sorted within each value to begin with, perhaps with these exceptions:

010160
51

unless you interpret those as single values (nothing to sort)... otherwise you would need to sort by each digit, like 000116 and 15.

So maybe all you need to do is trim out all spaces?

[jonamg]

Ray

Can i get any sample routine that sorts the values in the column?

[ray.wurlod]

I don't have a parallel routine. The logic is a simple insertion sort. Here it is as a server routine (language DataStage BASIC).

Code: Select all

FUNCTION SortStringChars(TheString)
EQU MAX_CHARS TO 1000
DIM Chars(MAX_CHARS)
If UnAssigned(TheString) Or IsNull(TheString)
Then
   Ans = @NULL
End
Else
   Ans = ""
   MatParse Chars From TheString Using "" Setting CharCount
   For ch = 1 To CharCount
      TheChar = Chars(ch)
      Locate TheChar In Ans Setting Pos
      Then
         Ins TheChar Before Ans<Pos>
      End
      Else
         Ins TheChar Before Ans<Pos>
      End
   Next ch
   Convert @FM To "" In Ans
End
RETURN(Ans)