Wild card search in AS/400 query

tracyv29

40 pts.

Tags:

AS/400

AS/400 Query

Wildcard

How do I find records that actually have percent sign in them using the like '% "%" %' when the % is the wild card?

Software/Hardware used:
AS/400 Query

Asked: September 17, 2012 8:06 PM Last updated: September 18, 2012 12:53 PM

Answer Wiki

Last updated: September 17, 2012 9:35 PM GMT

CharlieBrowne62,340 pts.
History
Contributors
- Ordered by most recent
- CharlieBrowne62,340 pts.

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

Is it always in the same position in the file?

Discuss This Question: 8 Replies

TomLiotta Sep 17, 2012 10:48 PM GMT

What query tool are you using? OPNQRYF? SQL? Query Manager? Query/400? Web Query? ODBC? Something else? -- Tom

125,585 pointsBadges:

report
tracyv29 Sep 18, 2012 2:20 PM GMT

I have the same question. I want to find embedded percent signs as well as embedded apostrophes within a text field using AS/400 Query. Some of our text fields contain only a % or an ' and I want to use the LIKE.

40 pointsBadges:

report
BigKat Sep 18, 2012 2:50 PM GMT

You use the ESCAPE clause in the SQL statement to declare a symbol to mark the special character as a literal character. SELECT * FROM TABLE WHERE C1 LIKE '%+%%' ESCAPE '+' first '%' is wild card, '+%' is a character literal of a percent sign, and the third '%' is wild card; therefore, above will find any row where C1 contains a percent sign

9,110 pointsBadges:

report
BigKat Sep 18, 2012 2:57 PM GMT

single quote is just SELECT * FROM TABLE WHERE C1 LIKE '%''%' single quote, percent sign, single quote, single quote, percent sign, single quote

9,110 pointsBadges:

report
tracyv29 Sep 18, 2012 2:58 PM GMT

I want to know if it is possible to do in AS/400 Query, not SQL

40 pointsBadges:

report
philpl1jb Sep 18, 2012 7:35 PM GMT

No. A query/400 can be converted to QM Query and the resultant SQL code can be edited as shown by BigKat.

54,090 pointsBadges:

report
tracyv29 Sep 18, 2012 7:50 PM GMT

Not the answer I was hoping for, but thanks guys!

40 pointsBadges:

report
TomLiotta Sep 19, 2012 0:32 AM GMT

I want to know if it is possible to do in AS/400 Query, not SQL A SQL query is an AS/400 query; there are many kinds of AS/400 queries. If you mean that you actually want to use the old, practically obsolete, inefficient Query/400 or Query for iSeries product, I don't know of a good way to escape the '%' symbol without using SQL. (V5R3 at least can show "Query" as the product name in the list of installed products.) One might exist; I just haven't seen one. The best answer is simply not to keep using the old query product for anything new, and convert old queries to new ones. (That doesn't apply to the recent Web Query product.) Some of our text fields contain only a % or an ‘ and I want to use the LIKE. If a field contains only a percent or apostrophe symbol, the LIKE function shouldn't be used. Just use "equal". Tom

125,585 pointsBadges:

report