ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to find Position of ceratin string in SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to find Position of ceratin string in SQL

    Hi All,

    Sorry to start a new thread....................but there was no option to reach you all......

    I am having a problem to find the position 'X' in a field (i.e. ARDIAG) as shown below.

    Diagnostic array

    X X

    position('X' in ARDIAG)


    SELECT position('X' in ARDIAG) FROM FILE1 WHERE x =95 gives me 12, for the first 'X' ..

    How can I know the 2nd 'X' position.......reply soon..
    Cheers
    PKBUNU

  • #2
    Re: How to find Position of ceratin string in SQL

    Is 12 the RRN of the record that has the first 'X'? or is that that the position in the first ARDIAG string? How big is your field?
    Your future President
    Bryce

    ---------------------------------------------
    http://www.bravobryce.com

    Comment


    • #3
      Re: How to find Position of ceratin string in SQL

      ARDIAG - Diagnostic Array - Field Name (100 Characters)

      Diagnostic array

      [ X X]

      Its not the RRN. In this field, only 'X' is there. The position of 'X' will tell us the error. i.e. the above field contains two 'X' but at different positions.
      Cheers
      PKBUNU

      Comment


      • #4
        Re: How to find Position of ceratin string in SQL

        I am making a bunch of assumptions with this one but if the field only contains X and you want the right most X use the Length Operator as follows:

        PHP Code:
        Select
              Length
        (RTRIM(ARDIAG))
        From (
               
        Select
                      
        '            X    X                 ' as ARDIAG
               From
                    Sysibm
        .Sysdummy1
             
        ) as sample 
        Last edited by kpmac; November 15, 2007, 01:06 PM.
        Predictions are usually difficult, especially about the future. ~Yogi Berra

        Vertical Software Systems
        VSS.biz

        Comment


        • #5
          Re: How to find Position of ceratin string in SQL

          Hi Kpmac

          Thanks a lot. Here is the statement from which I got the 2nd position of 'X'

          SELECT Length(RTRIM(ARDIAG)) FROM xyzzz WHERE NO =82295

          but again one doubt, If say there will be another 'X' then how could it be possible to get that
          Cheers
          PKBUNU

          Comment


          • #6
            Re: How to find Position of ceratin string in SQL

            I cannot figure out how to create such a select statement but build something risky and complicated, therefore error prone.
            IMHO your best bet is either to create a program that inspects the ARDIAG field or create a sql or HLL external function that returns all the found positions separated by comma.

            If sql is an option, the function should look like this.

            Code:
            -- Drop [B]Specific Function MyFunctionX[/B];                 
                                                              
            Create Function MyFunction ( ArDiag char(100) )     
             Returns varchar(400)                             
             Language SQL                                     
             [B]Specific MyFunctionX[/B]
             Not Deterministic                                
             Called On Null Input                             
             Disallow Parallel                                
             Begin                                            
                                                              
               Declare Pos Varchar(400) Default ' ';          
               Declare Idx SmallInt Default 0;                
                                                              
               Set Pos = ' ';                                 
               Set Idx = 0;   
            
               ScanARDIAG:                                                  
               LOOP                                                         
                                                                            
                 Set Idx = Idx + 1;                                         
                 if Idx <= 100 Then                                         
                    If substr(ARDIAG, Idx, 1) = 'X' Then   -- 'X' found                 
                       If Length(Trim(Pos)) = 0 Then    -- First time                 
                          Set Pos = Char(Idx);                              
                       Else                                                 
                          Set Pos = RTrim(Pos) concat ',' concat Char(Idx); 
                       End If;                                              
                    End If;                                                 
                 Else                                                       
                   Leave ScanARDIAG;                                        
                 End If;                                                    
                                     
                END LOOP ScanARDIAG; 
                                     
                Return Pos;          
                                     
             End
            Then, use it as shown below.
            Code:
            SELECT ..., [B]MyFunction(ARDIAG)[/B] as "Where is X ?", ... FROM FILE1 WHERE ...
            Last edited by Mercury; November 16, 2007, 11:05 AM.
            Philippe

            Comment


            • #7
              Re: How to find Position of ceratin string in SQL

              Thanks Mercury...its wonderful...
              Cheers
              PKBUNU

              Comment

              Working...
              X