LIKE pattern matching in AS/400

820 pts.
Tags:
AS/400
AS/400 Query
Hi, I have table with one column(ID), values are in the format of '123-456-7890'. Now I need to fetch all the rows from the table whose ID values are of the  same format. I tried to query as Select * from file where ID like ('%___-___-____%'). But no records found. Kindly let me know, how to acquire this. Thanks

Software/Hardware used:
AS400 database

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try selecting records in as select * from file where substr(id,4,1) = ‘-’ and substr(id,8,1) = ‘-’.

Discuss This Question: 28  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
  • hunshabbir7
    Remove parenthesis around LIKE clause and try again. I run the query and got the results.
    1,440 pointsBadges:
    report
  • HABBIE
    Query is running But, it is not fetching any records even, if I removed the braces too.
    Select * from file where ID like '%___-___-____%'.
    820 pointsBadges:
    report
  • philpl1jb

    If I've counted correctly you want rows with a '-' in the 4th and 8th locations.  This should work.

    Select * from file where Substring(ID,4,1) = '-' and Substring(ID,8,1) = '-'

     

     

    49,940 pointsBadges:
    report
  • BigKat
    Just so you know why you did not get any rows with the following: Select * from file where ID like '%___-___-____%'.

    that query is literally looking for a value that is 0 or more characters followed by the literal text string "___-__-____" followed by 0 or more characters
    8,200 pointsBadges:
    report
  • HABBIE
    Hi Philp,

    It is still not fetching any records.
    820 pointsBadges:
    report
  • BigKat
    if this is that SSN column from one of your other questions, the pattern is 123-45-6789 so the are in position 4 and 7
    8,200 pointsBadges:
    report
  • HABBIE
    Hi BigKat,

    kindly forgive me If I am wrong.I am new to this technology.
    I tried with all the scenario's such as
    Select * from file where ID like '%___-___-____%'
    Select * from file where ID like '___-___-____'.
    But My screen is empty It is not fetching any records.
    Kindly help me please
    820 pointsBadges:
    report
  • HABBIE
    No,
    Thats a different table.It is a new table from which I need to fetch the records in this format itself.
    123-345-5678.
    820 pointsBadges:
    report
  • HABBIE
    Thing is what,
    I tried with the following & this got worked.
    SELECT * FROM FILE WHERE ID LIKE '__________'.
    Now I can watch my desired output for the same format as 123-345-5674.
    820 pointsBadges:
    report
  • BigKat
    if you put the ___ and the - in the like clause, the will literally have to be in the field to be selected so "1___-___-____2" would be selected but "1999-999-99992" would not
    8,200 pointsBadges:
    report
  • carlosdl
    If you are interested only in records with the format in your example, you should not need the percentage signs.

    Something like this should be enough:

    LIKE '___-___-____'

    Make sure you have the correct number of underscores on each position.
    69,365 pointsBadges:
    report
  • carlosdl
    @BigKat, in standard SQL the underscore (_) works as a wildcard that matches exactly one character, so those don't need to be literally in the string.
    69,365 pointsBadges:
    report
  • HABBIE
    Hi carlo,

    If I tried with what u said,I mean '___-___-____'
    I can't get any kind of out put.
    820 pointsBadges:
    report
  • HABBIE
    yes.Thanks Carlo
    820 pointsBadges:
    report
  • carlosdl
    @HABBIE Looking at the example you said worked ('__________'), it contains 10 characters.  If you got the desired rows with that condition, it would mean that those numbers are stored without the hyphens (-), and they are only being used to format the output at display time.
    69,365 pointsBadges:
    report
  • BigKat
    @carlosdl,  I thought there was a way to wildcard a single character, but when I tried it didn't work, so I assumed the _ was not a wildcard character BUT...

    if I tried a 15A field  like '___1' (meaning 4 characters, ends in 1) it doesn't return anything. I had to code like '___1 %'.  since apparently you need to specify all the characters in a like test

    8,200 pointsBadges:
    report
  • HABBIE
    I don't know whether the numbers are stored with Hyphens are not. I am not an expert with this technology.recently started working with this.I am learning from your people now.
    But using this ('__________'), I got my desired out put.
    might be what u said is right. Storage differs from visibility.
    Thanks for the clarification. 

    I request you to kindly have look on this, link please. As ,I am struggling with this.

    http://itknowledgeexchange.techtarget.com/itanswers/sqlrpgle-program-results-nothing/

    Thanks 

    820 pointsBadges:
    report
  • carlosdl
    @BigKat that is interesting.  So it becomes a mix of SQL and AS400 specific stuff.
    69,365 pointsBadges:
    report
  • BigKat
    I wonder if that is a "bug" in the SQL implementation (at least in v6r1)
    8,200 pointsBadges:
    report
  • carlosdl
    @HABBIE I've never used SQLRPGLE, so I won't be able to help there, but I'm sure someone else will do it.
    69,365 pointsBadges:
    report
  • HABBIE
    @carlosdl Thanks for the help. Thank you so much.
    820 pointsBadges:
    report
  • hunshabbir7
    previously I run this query in my V5R1 and now I run the same on my V6R1 both give me the same result. I also have fields like this. I wonder why it is not working at your end.
    1,440 pointsBadges:
    report
  • HABBIE
    @hunshabbir7 Even, It totally depends on the length described for that field, It is using that Hyphen for display purpose itself. I specified the length of this field as 10( 10 digits + 2 hyphen symbols).  
    820 pointsBadges:
    report
  • philpl1jb

    To discover more about the fields in a file use the command display File Field Description:

        DSPFFD myFile

     

    Well this piqued my curiosity.

    Select * from myfile where myNumFld like '_________'

    works .. apparently it casts myNumFld as Character and provided you select enough wildcards for characters or wildcard the rest of the string it works.

    Select * from myfile where myNumFld like '______%'

    And you can test for a specific value in any position.

    Select * from myfile where myNumFld like '______4__'

    Obviously myNumFld can't have a - in it where you were seeing it.  and only a numField can have a format assigned to make a - appear in it.

     

    49,940 pointsBadges:
    report
  • hunshabbir7
    you said that the length of the field is 10 then how you can match a hyphen symbol out of 1234567890, you also said that this( '__________' ) thing gives results. It means you want to show a 10 length character field into formatted result. I think you should use EDTWRD keyword after matching records.
    1,440 pointsBadges:
    report
  • HABBIE
     ....+....1....+....2....+....3....+....4....+....5....+....6....+....
     NAME         SSN_ID_1             SSN_ID_2     SSN_ID_3     SSN_ID_4 
     WUYTYTUPUQ  650195181  8656-1757-0175-1881  571-77-1771  270 72 6868 
     IUWYITYTOI  529988270  2689-2767-2067-0270  927-62-6722  276 72 6727 
     KJHJGKHSKS  610969690  9262-9696-9262-8969  252-56-2656  236 52 6560 
     TEWYTYTUPU  926720767  9286-3276-7260-7027  207-62-7620  703 27 0720 
     REYEYEYEYY  151515115  2525-2326-2637-3778  232-37-2272  463 63 4646 
     HSHARRAHNH  155612361  7327-2373-7237-3288  161-37-4741  436 61 6344 

    This is a part of my file,where
    ssn_id_1,ssn_id_3,ssn_id_4 are of the same length.

    In order to find out the pattern through SQL,
    SELECT * FROM FILE WHERE SSN_ID_1 LIKE ('_________').
    This results the exact fields. But, the same I need to apply for SSN_ID_2 & SSN_ID_3, as the length of 3 columns are the same. 

    How to differentiate between these three, as 
    SELECT * FROM FILE WHERE SSN_ID_3 LIKE ('___-__-___').
    The above query results nothing for column3 
    820 pointsBadges:
    report
  • philpl1jb

    Through all these threads, I think we've said this before. 

    Apparently SSN_ID_3 is a numeric field.  It can not contain hyphens just digits.  It can appear to contain hyphens because there is an edit word attached to the description..  All entries in this column will appear to have hyphens unless the number is lower than 7 digits and the format doesn't force the hyphens. 

    There would be business purposes to look for a specific SSN

    If SSN_ID_3 = 123451234

    What are you trying to achieve?

    49,940 pointsBadges:
    report
  • HABBIE
    Thanks Philp , now I came to know the concept behind this.Thanks for your huge support 
    820 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