Need to identify the next sequence of number in a character field in DB2

150 pts.
Tags:
AS/400 V6r1m0
IBM DB2
I have a scenario where I need to find the next sequence of numbers in a character field. I found the starting position by using the LOCATE command but need to find the next sequence in the same row if anything. For Example: Issue Number : 01 Card Number 1234 5678 9012 3456. I found the starting position of '0' but need to find the position of digit '1' after card number. Suppose the start position of '0' is 16 means then the next number start position should be 31 as per the example above. I used MIN(LOCATE) and to find maximum value used MAX(LOCATE). But it is not giving correctly what i required in this case. I need this in QMQUERY or SQL in DB2. Thanks in advance. Saravanan D

Software/Hardware used:
AS/400 V6R1M0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    I used MIN(LOCATE)...   How? It doesn't make sense to use MIN(), nor MAX(). If all rows have a similar format for the column with that data, both of those functions will return the same value. They will also return single values for the entire table unless you are GROUPing BY the column with the data (which wouldn't make much sense).   Why do you need to use SQL for this? It can be done with SQL, but the data isn't formatted for efficient use by SQL.   At the least, you need to show us the SQL code you are using.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    nope .. I'm certain that I don't understand what you're not after.  As Tom said Max and Min search the entire database for the largest/smallest value in the field   If it's the second occurance of a string in a single record then here is a way to find it using locate.    select locate(:mystring, substring( Myfield, locate(:mystring, MyField ) + 1)) from MyFile   Please give us some examples of search value, entire string and desired result.  
    51,355 pointsBadges:
    report
  • Dsaran2007
    Sorry for the less information. Please find the code i used to identify the first starting number in a field and it works fine for me. Select T2.FSFLNA, -- Line Text  MIN(LOCATE(T1.NUMBER,T2.FSFLNA)) POSITION FROM LIBRARY/NUMBER T1, LIBRARY/DBFILE T2    WHERE (LOCATE(T1.NUMBER,T2.FSFLNA) != 0 Group By T2.FSFLNA Note: The FSFLNA is the field which contains the information in the file DBFILE and NUMBER is a parameter file which contains 0 to 9 number. Now i need to find the next number in the same field FSFLNA. It wont be in a defined format it can be in any position. For example like below i need to find. 1. Sim card number 89441 10064 30425 7482 mobile number        07515294898 has b 2. Card Number     :##3742 ###########32004 3. *ISSUE NUMBER  :16              CARD NUMBER:4917556315625229 Hope the information helps.
    150 pointsBadges:
    report
  • philpl1jb
    1 2 3 4 5 6 7 8 12345678901234567890123456789012345678901234567890123456789012345678901234567890 1. Sim card number 89441 10064 30425 7482 mobile number 07515294898 has b 1 2 2 3 6 8 3 9 5 0 2. Card Number :##3742 ###########32004 2 3 0 6 3. *ISSUE NUMBER :16 CARD NUMBER:4917556315625229 1 4 8 5
    51,355 pointsBadges:
    report
  • philpl1jb
    Sorry, the darn editor won't let me show the line spacing. I'm sorry, I have no idea what you expect the results to be or why you would want those results.  Perhaps you could tell us what answers you actually expect from these three examples.  I suspect that when I understand the question I will not enjoy solving it with SQL.  It already looks like a bad fit.  I'm sure your method won't extend the way you want it to.  Marvin: I've been talking to the main computer. Arthur: And? Marvin: It hates me.
    51,355 pointsBadges:
    report
  • philpl1jb
    not sure what outcome you wantnot sure that SQL is the best way to go
    51,355 pointsBadges:
    report
  • TomLiotta
    The need is apparently to find the second (or next) sequence of digits in a string. The first sequence is found, though I haven't verified that. In general I agree that SQL is not the best way to do it regardless of whether it can be done or not. (It can be. It's just very ugly and inefficient.) -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    So if I had to solve this problem, I would begin by creating the sql functions:   FindNextDigit with inputs of the string and start position and return of the location or 0.    and    FindNextNotDigit  with inputs of the string and start position and return of the location or 0.     
    51,355 pointsBadges:
    report
  • Dsaran2007
    The requirement is to find the card number in that field and mask first 12 digits but the problem is the card number is in any position. It may be like this Card Number : 1234 5678 9123 4567 this is normal and no probelm for me. But if it is like this then the problem comes for me Issue Number : 01 Card Number : 1234 5678 9123 4657 or Sim card number 89441 10064 30425 7482 mobile number 07515294898 Card Number 1234 5678 9123 4657 Am i clear in what i want now? If SQL/QMQRY is not possible means is there anyway like RPG/RPGLE is possible for this?
    150 pointsBadges:
    report
  • TomLiotta
    If SQL/QMQRY is not possible means is there anyway like RPG/RPGLE is possible for this?   It can be done, but it's going to require a procedure, not just a SQL statement. And it will return a result set because either you have an unknown number of values being returned or you need to process the same field an unknown number of times. (I can imagine that it might be done with a recursive CTE, but I'm not sure what you'll do with it.)   I wouldn't do it with SQL. Depending on how many rows need to be processed and quickly it must be done, I might use a REXX proc to do it. REXX is very good at parsing strings and extracting 'words'. The very first call can take setup time for the REXX environment, but a series of calls isn't as bad. The advantage would be small, elegant code.   ILE RPG could certainly do it, and it's probably the best choice for you.   Tom
    125,585 pointsBadges:
    report
  • Dsaran2007
    Thanks tom. I will go with ILE and I am not aware of REXX procedure.
    150 pointsBadges:
    report
  • philpl1jb
    RPG is an excellent choice for this process. 
    51,355 pointsBadges:
    report
  • TomLiotta
    Though ILE RPG might be your best choice because of familiarity, after a choice of REXX, I might instead choose a COBOL procedure. After INSPECT tells you how many "words" are in the string, the UNSTRING verb could break the "words" into a list. Each "word" would then easily be determined to be numeric or not. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following