Sort the values in the column in ascending order
Moderators: chulett, rschirm, roy
Sort the values in the column in ascending order
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?
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?
Re: Sort the values in the column in ascending order
You mean to say the value is Integer for all the records?
DS User
DS User
Re: Sort the values in the column in ascending order
its varchar(7)
Re: Sort the values in the column in ascending order
I mean to ask, the values for vc(7) is numbers or ...?
DS User
DS User
Re: Sort the values in the column in ascending order
yes.It contains only inter values.
Re: Sort the values in the column in ascending order
You can use convert function get this.
Trim(Convert(Convert("0123456789","",In.Col),"",In.Col))
DS User
Trim(Convert(Convert("0123456789","",In.Col),"",In.Col))
DS User
Re: Sort the values in the column in ascending order
Update : the column also contains characters as well
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Sort the values in the column in ascending order
The example you mentioned is larger than what will fit in varchar(7): '2 4 1 5 7'.jonamg wrote:Update : the column also contains characters as well
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Re: Sort the values in the column in ascending order
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
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
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?
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?
Choose a job you love, and you will never have to work a day in your life. - Confucius
Re: Sort the values in the column in ascending order
Ray
Can i get any sample routine that sorts the values in the column?
Can i get any sample routine that sorts the values in the column?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.