I read the regEx dev works article and others online. Nothing speaks about any special treatment the underscore(_) character gets in DB2, yet when I give the following command :
* select tabname from syscat.tables where tabschema='CCDB' AND TABNAME LIKE '%_2' WITH UR;

Arunz_VW2

I don't need this table to be included. All I needed to know was how many tables were like 'Arun_2' etc.
I got around using the sub-string with the length function, but really wanted to know why the '_' char in the Like condition was getting an un-documented(plz link if it in fact is) special treatment.

On Monday, August 6, 2012 9:35:14 PM UTC+2, Arun Srini wrote:
The use of the underscore character is documented:

http://publib.boulder.ibm.com/infoce.../r0000751.html

"A simple description of the use of the LIKE predicate is that the pattern is used to specify the conformance criteria for values in the match-expression, where:
- The underscore character (_) represents any single character.
- The percent sign (%) represents a string of zero or more characters.
- Any other character represents itself."

I'll give you that the use of the percent sign is indeed much more widely known.

--
Frederik Engelen

On 2012-08-06 22:20, Frederik Engelen wrote:
In addition, this is how it is defined in sql since - at least - SQL92
(Feature id E061-05). Oracle, MySQL, Postgres and MSSQL all does it the
same way:

http://www.dba-oracle.com/tips_oracl...characters.htm
http://dev.mysql.com/doc/refman/4.1/...functions.html
http://www.postgresql.org/docs/8.3/s...-matching.html
http://msdn.microsoft.com/en-us/libr...sql.90%29.aspx

But, I get the impression that it is not commonly known, and the
question is raised every now and then in different forums.

There is a nice tool at the mimer site where one can verify a certain
construct against SQL92, SQL99 and SQL2003

http://developer.mimer.se/validator

HTH
/Lennart

On Monday, 6 August 2012 18:16:54 UTC-5, Lennart Jonsson wrote:
Thanks Lennart.. Those were nice learning links.

You may want to use a escape character.

Please see manual.
http://publib.boulder.ibm.com/infoce.../r0000751.html

Here is an example using ESCAPE.

------------------------------ Commands Entered ------------------------------
WITH
test_data( tabname ) AS (
VALUES
'Arunz_VW2'
, 'Arun_2'
, 'Arun__2'
, 'Arun2'
)
SELECT tabname
FROM test_data
WHERE tabname LIKE '%+_2' ESCAPE '+'
WITH UR
;
------------------------------------------------------------------------------

TABNAME
---------
Arun_2
Arun__2

2 record(s) selected.


Note: db2level

D:\IBM\SQLLIB_V97\tools>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09075" with
level identifier "08060107".
Informational tokens are "DB2 v9.7.500.4299", "special_27924", "IP23286_27924",
and Fix Pack "5".