AS/400 Query: How to select records with digits and/or characters

15 pts.
Tags:
AS/400
Query
Hi, I'm doing a cleanup and I need your help. 1) Is it possible by using As/400 query to select records which containing digit or signs (1, 2, @, -, & Etc.). For example, field "customer name" with digits/signs. 2) Is it possible by using As/400 query to select records which containing characters (a, b, c Etc.). For example, field "Postal Code" with characters. 3) Is it possible by using As/400 query to select records which are in uppercase or lowercase characters? Thanks a lot! MZ

Answer Wiki

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

Ezis,

The last time I checked AS/400 was an IBM Mid-Frame operating system. What database software
is being used? DB2? Oracle? Something else?

Anyway, if the DB software being used is some form of SQL database, then yes, you can do all the
things you asked. I’ll be glad to give examples if you can provide more specific information about
the database software.

One query that I could use (MSSQL query) would find all records that have “digit or signs” in the customer_name column of the customer table and would be formed like this:

<b>select * from customer where customer_name like ‘%[12@-&]%’</b>

Discuss This Question: 8  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
  • Gilly400
    Hi, 1. This is possible, but it's a bit messy. In the record selection screen :- For the numerics :- CUSTNAME LIKE '%0%' OR CUSTNAME LIKE '%1%' OR CUSTNAME LIKE '%2%' etc. For the other characters :- OR CUSTNAME LIKE '%@%' OR CUSTNAME LIKE '%-%' OR CUSTNAME LIKE '%&%' etc. 2. This is also possible, by the same method as 1. 3. I'm not to sure what you mean by this question. Can you explain what you want to achieve? Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • philpl1jb
    Right you can scan a field for lower case with 26 of Martins wild card or's CUSTNAME LIKE ‘%a%’ OR CUSTNAME LIKE ‘%b%’ OR CUSTNAME LIKE ‘%c%’ etc. Not nice, not fast if you have volumn, but it will work.
    49,720 pointsBadges:
    report
  • Apickel
    If you're lucky enough that the database on this machine is Oracle 10g or higher you can use the regexp_like function - very cool.
    350 pointsBadges:
    report
  • Gilly400
    Hi, There's lots of nice tools available on other platforms for this sort of thing - but right now the question is about AS400 Query which is a reporting tool - not SQL. Unfortunately Query is much more restricted in what it can do, but it's also safer than letting end users start SQLing data files. AS400 Query Manager would be a better option if available as it allows SQL commands, however it's also much more complicated to use and allows updates. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • Mrnaturl
    I find that numeric fields are not allowed with the LIKE statement in regular AS400 Query. Attempting to do so returns a message " Field not allowed with LIKE or NLIKE test."
    145 pointsBadges:
    report
  • philpl1jb
    You're right Mrnaturl, LIKE can only be used to wildcard character fields However, EZIS was searching for bad characters entered into char fields. Numeric fields can only have digits. But if you need to search a numeric field you will need to change it to char first. Use Define Result Fields to create a new character type field from the numeric field using the digits function. Field Expression NewFld digits(OldFld) Then you can use %LIKE% on the NewFld -- but since it was a number it will only contain digits. Phil
    49,720 pointsBadges:
    report
  • Apickel
    Martin, Thanks for clearing that up for me - I know nothing about AS400 Query. Looks like there is a limit to what can be done for Ezis in this case.
    350 pointsBadges:
    report
  • Ezis
    Hello all, Thanks a lot for your answers. I was aware that it is possible to do it by using the "messy" way that some of you mentioned but I was hopping maybe there is a "secret" way not to go through this mess. MZ
    15 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