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:
  1.      h nomain
  2.       //----------------------------------------------*
  3.       // SORTWORDS - Sort String Elements             *
  4.       // luis rodriguez - 2012-10-08                  *
  5.       //----------------------------------------------*
  6.       // CRTRPGMOD MODULE(SORTWORDS)
  7.       // CRTSRVPGM SRVPGM(SORTWORDSS) MODULE(SORTWORDS) EXPORT(*ALL)
  8.      DSORTWORDS        PR           256a   varying
  9.      D input_data                   256a   varying
  10.  
  11.      PSORTWORDS        B                   export
  12.      DSORTWORDS        PI           256a   varying
  13.      D input_data                   256a   varying
  14.  
  15.      D*-----------------
  16.      D Elements        S             80    Varying Dim(98)
  17.      D Delimiter       S             01    inz(' ')
  18.      D String          S            512    Varying
  19.      D I               S             04S 0
  20.      D J               S             04S 0
  21.      D Pos             S             04S 0
  22.      D Max_Elements    S             02S 0 inz(98)
  23.      D output_data     S            256a   varying
  24.       /free
  25.        Clear output_data;
  26.        input_data = %trim(input_data);
  27.        If input_data <> '' ;
  28.           EXSR Parse_Input;
  29.           IF I > 0;
  30.             EXSR Create_OutputData;
  31.           ENDIF;
  32.        ENDIF;
  33.        return output_data;
  34.  
  35.        //---------------------------------------
  36.         BEGSR Parse_Input ;
  37.            Clear i;
  38.            Clear Elements;
  39.            String = input_data ;
  40.  
  41.            For i = 1 to Max_Elements;
  42.              Pos = %scan(Delimiter:String);
  43.              IF POS = 0;
  44.                Elements(i)= %trim(String); // last element
  45.                Leave;
  46.              ELSE;
  47.                Elements(i)= %trim(%subst(String:1:Pos-1));
  48.              ENDIF;
  49.              String = %subst(String:Pos + 1);
  50.            ENDFOR;
  51.         ENDSR;
  52.         //--------------------------------------------------------------*
  53.         BEGSR Create_OutputData;
  54.           SORTA %SUBARR(Elements : 1: i );
  55.           For j = 1 to i;
  56.             output_data = output_data + Elements(j) + Delimiter;
  57.           ENDFOR;
  58.           output_data = %trim(output_data);
  59.         ENDSR;
  60.         //--------------------------------------------------------------*
  61.  
  62.       /end-free
  63.  
  64.      PSORTWORDS        e 
  65. ***********************************************
  66.  After compiling, create the SQL function with the following script:
  67. Create Function @520/SORTWORDS(input_data VarChar(256))
  68.     Returns VarChar(256)
  69.     Language RPGLE
  70.     Parameter Style General
  71.     Deterministic
  72.     No SQL
  73.     Returns Null on Null Input
  74.     Not Fenced
  75.     External Name '@520/SORTWORDSS(SORTWORDS)'
  76.     ;
© 2004-2019 by midrange.com generated in 0.005s valid xhtml & css