AS/400 Query: How to select records with digits and/or characters
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
Software/Hardware used:
Software/Hardware used:
ASKED:
December 30, 2008 8:24 AM
UPDATED:
January 1, 2009 2:28 PM
Answer Wiki:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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.
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.
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.
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.”
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
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.
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