IBMiAccess




Results 1 to 7 of 7

Thread: How to find Position of ceratin string in SQL

  1. #1
    Analyst pkbunu's Avatar
    Join Date
    Oct 2007
    Location
    Wayne, NJ
    Posts
    74
    Rep Power
    796

    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. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Programmer/Analyst I bryce4president's Avatar
    Join Date
    Apr 2007
    Location
    Lewisburg, PA
    Age
    31
    Posts
    2,895
    Rep Power
    4661

    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

  4. #3
    Analyst pkbunu's Avatar
    Join Date
    Oct 2007
    Location
    Wayne, NJ
    Posts
    74
    Rep Power
    796

    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

  5. #4
    Ex - Solutions Architect kpmac's Avatar
    Join Date
    Aug 2005
    Location
    NJ
    Age
    36
    Posts
    454
    Rep Power
    2441

    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 15th, 2007 at 02:06 PM.
    Predictions are usually difficult, especially about the future. ~Yogi Berra

    Vertical Software Systems
    VSS.biz

  6. #5
    Analyst pkbunu's Avatar
    Join Date
    Oct 2007
    Location
    Wayne, NJ
    Posts
    74
    Rep Power
    796

    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

  7. #6
    Programmer Mercury's Avatar
    Join Date
    Feb 2007
    Location
    Paris, France
    Posts
    1,831
    Rep Power
    5435

    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 Specific Function MyFunctionX;                 
                                                      
    Create Function MyFunction ( ArDiag char(100) )     
     Returns varchar(400)                             
     Language SQL                                     
     Specific MyFunctionX
     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 ..., MyFunction(ARDIAG) as "Where is X ?", ... FROM FILE1 WHERE ...
    Last edited by Mercury; November 16th, 2007 at 12:05 PM.
    Philippe

  8. #7
    Analyst pkbunu's Avatar
    Join Date
    Oct 2007
    Location
    Wayne, NJ
    Posts
    74
    Rep Power
    796

    Re: How to find Position of ceratin string in SQL

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

  9. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. Replies: 1
    Last Post: November 15th, 2007, 01:30 AM
  2. wdsc find string cool beans
    By jamief in forum RDP/RDi/WDSC - Tips & Tricks
    Replies: 0
    Last Post: June 27th, 2007, 02:09 PM
  3. using GREP to find string in message
    By jamief in forum Tips for the Iseries/AS400
    Replies: 0
    Last Post: August 2nd, 2006, 04:10 PM
  4. %check sqlrpgle
    By christinaf in forum RPG/RPGLE
    Replies: 1
    Last Post: January 19th, 2006, 03:56 AM
  5. Replies: 3
    Last Post: July 16th, 2004, 05:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •