To add more (having two nodes "A_B" and "_B" defined for testing
purposes):
"select node_name from nodes where node_name like '\_%' escape '\'
NODE_NAME
------------------
_B
select node_name from nodes where node_name like '%\_%' escape '\'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
select node_name from nodes where node_name like '%_\_%' escape '\'
NODE_NAME
------------------
A_B
"
Tested using TSM client 4.2.1.32 against servers v4.1.6/AIX &
v4.2.1.15/W2k
After check in DB2 manuals (my ultimate source for TSM SQL) there is a
statement "When escape characters are present in the pattern string, an underscore,
percent sign, or escape character can represent a literal occurrence of
itself. ". Using capital "A" as escape it works as with backslash:
"select node_name from nodes where node_name like 'A_%' escape 'A'
NODE_NAME
------------------
_B"
However using it to escape itself does not work:
"select node_name from nodes where node_name like 'AAA_%' escape 'A'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
"
it ought to return "A_B". I know, I know ... this is not documented for
TSM (only for DB2) thus I cannot object it works or not. But this may
narrow the search for the root of the problem.
Zlatko Krastev
IT Consultant
Andrew Raibeck <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
12.11.2002 20:36
Please respond to "ADSM: Dist Stor Manager"
To: [EMAIL PROTECTED]
cc:
Subject: Re: not like underscore
You can use ESCAPE '<escape char>' at the end of the WHERE clause to make
this work. For example, to use '\' as the escape character:
select node_name from nodes where node_name like '%\_%' escape '\'
Unfortunately I just discovered that this isn't working quite right. I can
use '%Y\_%' to find node names like 'ANDY_RAIBECK', but the '%\_%' pattern
produces no results for me (5.1.5.1 server version).
Oddly enough, this pattern: '%_\_%' does the trick. But that is a
circumvention only, and works only if the node name does not begin with an
underscore.
Sorry, I don't know enough about this problem to describe it better, as I
just discovered it a minute ago.
Regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)
The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.
Kai Hintze <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
11/12/2002 11:15
Please respond to "ADSM: Dist Stor Manager"
To: [EMAIL PROTECTED]
cc:
Subject: not like underscore
Oh wiseness of wizards of tsm-flavored SQL, how do you match an
underscore?
I need to find node names that do not contain an underscore ('_'). I
thought
that would be no problem, just "select node_name from nodes where
node_name
not like '%_%'", but that doesn't find anything. A bit more research
revealed that '_' is the single character match, so that expression parsed
out to something like "select node_names without any characters". Not what
I
wanted at all.
I've played with a couple of things to escape the underscore, but I
haven't
found anything that works yet. I thought I remembered reading something
about match and wildcards in this list, but all I can find in the archives
is the mention that underscore is the single character wildcard.
If I can't find an answer fairly quickly I will just dump everything to a
file and parse out the results with perl or something, but I really think
what I need to do could be done easily in SQL if I could just parse out
the
underscore.
I thank you in advance for any light you could shed on my difficulty.
- Kai.
