Jim Franz wrote:
How does one scan for special characters in a field with sql?
LOCATE(), POS(), or POSITION() scalar functions; read each for
any nuances that may be required for the data being scanned.
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscale.htm
Need to find percent sign - %, ampersand - &, and underscore - _
Could be in any position within a field.
Combine the TRANSLATE() scalar with one of the above scalars to
locate the converted character value.
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscatrans.htm
Translate all characters which need to be found, into one of those
three characters, and scan for that character in the result of that
expression. For example, to scan for all of percent, ampersand, and
underscore in the column QQTEXT of file QAQQINI in QSYS [which
requires a non-hex job CCSID]:
<code>
select substr(qqparm, 1, 31) as PARM
, substr(qqtext,
locate('&', translate(char(QQTEXT), '&&&', '%&_') ), 15
) as "Has %,&, or _"
from qsys/qaqqini
where locate('&', translate(char(QQTEXT), '&&&', '%&_') ) <> 0
PARM "Has %,&, or _"
PARALLEL_DEGREE _OF_TASKS--Spec
OPEN_CURSOR_CLOSE_COUNT _CURSOR_THRESHO
OPTIMIZE_STATISTIC_LIMITATION _NUMBER_OF_RECO
REOPTIMIZE_ACCESS_PLAN _REQUIRED--Do n
IGNORE_LIKE_REDUNDANT_SHIFTS %WLDCRD built-i
******** End of data ********
</code>
Is there perhaps a way to do a LIKE with a hex value?
An example of what can be done, to effect a scan for the hex
value 0xC1 in a string:
-- Find rows with an "A" in the STREET column
select * from qiws/qcustcdt
where street like '%' concat x'C1' concat '%'
When using a LIKE predicate to perform the scan for a specific
character, since a full table scan would be required anyhow, asking
on the WHERE clause for three separate ORed condistions is a
reasonable option instead of messing with TRANSLATE & LOCATE. For
the percent & underscore, just be sure to use the ESCAPE clause and
the specified escape character in the search string. For example:
<code>
select substr(qqparm, 1, 31) as PARM
, substr(qqtext, 131, 15) as TEXT
from qsys/qaqqini
where char(qqtext) like '%\%%' escape '\'
or char(qqtext) like '%&%'
or char(qqtext) like '%\_%' escape '\'
PARM TEXT
PARALLEL_DEGREE , rebuilds, and
OPEN_CURSOR_CLOSE_COUNT he number of cu
OPTIMIZE_STATISTIC_LIMITATION is determined
REOPTIMIZE_ACCESS_PLAN NO the query ac
IGNORE_LIKE_REDUNDANT_SHIFTS command %WLDCRD
******** End of data ********
</code>
Regards, Chuck
midrange.com
