midrange.com code scratchpad |
Name:
Sort words in a string, using SQL
|
Scriptlanguage:
Plain Text
|
Tabwidth:
4
|
Date:
10/08/2012 04:01:58 pm
|
IP:
Logged
|
|
Description:
In the thread http://archive.midrange.com/midrange-l/201210/msg00298.html
someone asked for a SQL method to order several short words contained into a string. Short of using recursive SQL (which I cannot do, as we are still using V5R3), I suggested writing a UDF. Here is a "quick and dirty" code that should do the trick.
select sortwords('word3 word2 word4 word1') from sysibm/sysdummy1 ;
should return: word1 word2 word3 word4
|
Code:
- h nomain
- //----------------------------------------------*
- // SORTWORDS - Sort String Elements *
- // luis rodriguez - 2012-10-08 *
- //----------------------------------------------*
- // CRTRPGMOD MODULE(SORTWORDS)
- // CRTSRVPGM SRVPGM(SORTWORDSS) MODULE(SORTWORDS) EXPORT(*ALL)
- DSORTWORDS PR 256a varying
- D input_data 256a varying
-
- PSORTWORDS B export
- DSORTWORDS PI 256a varying
- D input_data 256a varying
-
- D*-----------------
- D Elements S 80 Varying Dim(98)
- D Delimiter S 01 inz(' ')
- D String S 512 Varying
- D I S 04S 0
- D J S 04S 0
- D Pos S 04S 0
- D Max_Elements S 02S 0 inz(98)
- D output_data S 256a varying
- /free
- Clear output_data;
- input_data = %trim(input_data);
- If input_data <> '' ;
- EXSR Parse_Input;
- IF I > 0;
- EXSR Create_OutputData;
- ENDIF;
- ENDIF;
- return output_data;
-
- //---------------------------------------
- BEGSR Parse_Input ;
- Clear i;
- Clear Elements;
- String = input_data ;
-
- For i = 1 to Max_Elements;
- Pos = %scan(Delimiter:String);
- IF POS = 0;
- Elements(i)= %trim(String); // last element
- Leave;
- ELSE;
- Elements(i)= %trim(%subst(String:1:Pos-1));
- ENDIF;
- String = %subst(String:Pos + 1);
- ENDFOR;
- ENDSR;
- //--------------------------------------------------------------*
- BEGSR Create_OutputData;
- SORTA %SUBARR(Elements : 1: i );
- For j = 1 to i;
- output_data = output_data + Elements(j) + Delimiter;
- ENDFOR;
- output_data = %trim(output_data);
- ENDSR;
- //--------------------------------------------------------------*
-
- /end-free
-
- PSORTWORDS e
- ***********************************************
- After compiling, create the SQL function with the following script:
- Create Function @520/SORTWORDS(input_data VarChar(256))
- Returns VarChar(256)
- Language RPGLE
- Parameter Style General
- Deterministic
- No SQL
- Returns Null on Null Input
- Not Fenced
- External Name '@520/SORTWORDSS(SORTWORDS)'
- ;
|
|
|