By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

Parameter markers in LIKE clause

P: n/a
Hi!

Is it possible to use parameter markers like this:
1.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'

If I now set parameter 1 to '' (empty string) I don't get any rows back,
but if I run:
2.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
I get back data I expect.

So what does the value of parameter marker has to be, so that the SQL
1.) behaves as 2.)?

I know I could do it like this:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE ?
and set parameter to
'%%', but that is really not a solution, since we have a lot of SQLs
written like 1.) and we cannot just change them.

Best regards,
Kovi

--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jun 27 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Jun 17, 5:29 am, Gregor KovaŤ <gregor.ko...@mikropis.siwrote:
Hi!

Is it possible to use parameter markers like this:
1.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'

If I now set parameter 1 to '' (empty string) I don't get any rows back,
but if I run:
2.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
I get back data I expect.

So what does the value of parameter marker has to be, so that the SQL
1.) behaves as 2.)?

I know I could do it like this:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE ?
and set parameter to
'%%', but that is really not a solution, since we have a lot of SQLs
written like 1.) and we cannot just change them.

Best regards,
Kovi

--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Hi, Kovi.

Assuming you're on LUW 8.x, have a look at the SQL Ref. V1, pp.
216-220, which provides an in-depth discussion of LIKE. Of particular
note for you might be the following quote:

"If the pattern specified in a LIKE predicate is a parameter marker,
and a fixed-length character host variable is used to replace the
parameter marker, the value specified for the host variable must have
the correct length. If the correct length is not specified, the select
operation will not return the intended results. For example, if the
host variable is defined as CHAR(10), and the value WYSE% is assigned
to that host variable, the host variable is padded with blanks on
assignment. The pattern used is: 'WYSE% ' The database manager
searches for all values that start with WYSE and that end with five
blank spaces. If you want to search only for values that start with
'WYSE', assign a value of 'WSYE%%%%%%' to the host variable."

HTH,

--Jeff
Jun 27 '08 #2

P: n/a
Hmm, so if the field FIELD1 is VARCHAR(10000) (for example) then the
value for ? should be 'EXAMPLE(and 9993 spaces)'?
What if the ? is an empty string? Should I provide 10000 spaces?

Best regards,
Kovi

jefftyzzer pravi:
On Jun 17, 5:29 am, Gregor KovaŤ <gregor.ko...@mikropis.siwrote:
>Hi!

Is it possible to use parameter markers like this:
1.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'

If I now set parameter 1 to '' (empty string) I don't get any rows back,
but if I run:
2.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
I get back data I expect.

So what does the value of parameter marker has to be, so that the SQL
1.) behaves as 2.)?

I know I could do it like this:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE ?
and set parameter to
'%%', but that is really not a solution, since we have a lot of SQLs
written like 1.) and we cannot just change them.

Best regards,
Kovi

--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Hi, Kovi.

Assuming you're on LUW 8.x, have a look at the SQL Ref. V1, pp.
216-220, which provides an in-depth discussion of LIKE. Of particular
note for you might be the following quote:

"If the pattern specified in a LIKE predicate is a parameter marker,
and a fixed-length character host variable is used to replace the
parameter marker, the value specified for the host variable must have
the correct length. If the correct length is not specified, the select
operation will not return the intended results. For example, if the
host variable is defined as CHAR(10), and the value WYSE% is assigned
to that host variable, the host variable is padded with blanks on
assignment. The pattern used is: 'WYSE% ' The database manager
searches for all values that start with WYSE and that end with five
blank spaces. If you want to search only for values that start with
'WYSE', assign a value of 'WSYE%%%%%%' to the host variable."

HTH,

--Jeff
--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jun 27 '08 #3

P: n/a
On Jun 17, 10:19 pm, Gregor Kovańć <gregor.ko...@mikropis.siwrote:
Hmm, so if the field FIELD1 is VARCHAR(10000) (for example) then the
value for ? should be 'EXAMPLE(and 9993 spaces)'?
What if the ? is an empty string? Should I provide 10000 spaces?

Best regards,
Kovi

jefftyzzer pravi:
On Jun 17, 5:29 am, Gregor Kovaè <gregor.ko...@mikropis.siwrote:
Hi!
Is it possible to use parameter markers like this:
1.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'
If I now set parameter 1 to '' (empty string) I don't get any rows back,
but if I run:
2.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
I get back data I expect.
So what does the value of parameter marker has to be, so that the SQL
1.) behaves as 2.)?
I know I could do it like this:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE ?
and set parameter to
'%%', but that is really not a solution, since we have a lot of SQLs
written like 1.) and we cannot just change them.
Best regards,
Kovi
--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Hi, Kovi.
Assuming you're on LUW 8.x, have a look at the SQL Ref. V1, pp.
216-220, which provides an in-depth discussion of LIKE. Of particular
note for you might be the following quote:
"If the pattern specified in a LIKE predicate is a parameter marker,
and a fixed-length character host variable is used to replace the
parameter marker, the value specified for the host variable must have
the correct length. If the correct length is not specified, the select
operation will not return the intended results. For example, if the
host variable is defined as CHAR(10), and the value WYSE% is assigned
to that host variable, the host variable is padded with blanks on
assignment. The pattern used is: 'WYSE% ' The database manager
searches for all values that start with WYSE and that end with five
blank spaces. If you want to search only for values that start with
'WYSE', assign a value of 'WSYE%%%%%%' to the host variable."
HTH,
--Jeff

--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
I think you're OK with VARCHAR--it's only fixed-length character (e.g.
CHAR) variables that have this consideration. I guess in that case
you'd want to use the REPEAT function ;-).

--Jeff
Jun 27 '08 #4

P: n/a
Gregor KovaŤ wrote:
Hmm, so if the field FIELD1 is VARCHAR(10000) (for example) then the
value for ? should be 'EXAMPLE(and 9993 spaces)'?
What if the ? is an empty string? Should I provide 10000 spaces?
String comparisons

Character strings are compared according to the collating sequence
specified when the database was created, except those with a FOR BIT
DATA attribute, which are always compared according to their bit values.

When comparing character strings of unequal lengths, the comparison is
made using a logical copy of the shorter string, which is padded on the
right with blanks sufficient to extend its length to that of the longer
string. This logical extension is done for all character strings,
including those tagged as FOR BIT DATA.
Description of LIKE predicate apttern:

Let m denote the value of match-expression and let p denote the value of
pattern-expression. The string p is interpreted as a sequence of the
minimum number of substring specifiers so each character of p is part of
exactly one substring specifier. A substring specifier is an underscore,
a percent sign, or any non-empty sequence of characters other than an
underscore or a percent sign.
The result of the predicate is unknown if m or p is the null value.
Otherwise, the result is either true or false. The result is true if m
and p are both empty strings or there exists a partitioning of m into
substrings such that:

* A substring of m is a sequence of zero or more contiguous
characters and each character of m is part of exactly one substring.
* If the nth substring specifier is an underscore, the nth
substring of m is any single character.
* If the nth substring specifier is a percent sign, the nth
substring of m is any sequence of zero or more characters.
* If the nth substring specifier is neither an underscore nor a
percent sign, the nth substring of m is equal to that substring
specifier and has the same length as that substring specifier.
* The number of substrings of m is the same as the number of
substring specifiers.

Thus, if p is an empty string and m is not an empty string, the result
is false. Similarly, it follows that if m is an empty string and p is
not an empty string (except for a string containing only percent signs),
the result is false.

The predicate m NOT LIKE p is equivalent to the search condition NOT (m
LIKE p).
You may also consider using '_' instead of or in addition to '%' in LIKE
predicate pattern.

Jan M. Nelken
Jun 27 '08 #5

P: n/a
So why SQL-s:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
and
SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'
where I set ? to '' or even NULL

don't produce same results?

Best regars,
Kovi

Jan M. Nelken pravi:
Gregor KovaŤ wrote:
>Hmm, so if the field FIELD1 is VARCHAR(10000) (for example) then the
value for ? should be 'EXAMPLE(and 9993 spaces)'?
What if the ? is an empty string? Should I provide 10000 spaces?

String comparisons

Character strings are compared according to the collating sequence
specified when the database was created, except those with a FOR BIT
DATA attribute, which are always compared according to their bit values.

When comparing character strings of unequal lengths, the comparison is
made using a logical copy of the shorter string, which is padded on the
right with blanks sufficient to extend its length to that of the longer
string. This logical extension is done for all character strings,
including those tagged as FOR BIT DATA.
Description of LIKE predicate apttern:

Let m denote the value of match-expression and let p denote the value of
pattern-expression. The string p is interpreted as a sequence of the
minimum number of substring specifiers so each character of p is part of
exactly one substring specifier. A substring specifier is an underscore,
a percent sign, or any non-empty sequence of characters other than an
underscore or a percent sign.
The result of the predicate is unknown if m or p is the null value.
Otherwise, the result is either true or false. The result is true if m
and p are both empty strings or there exists a partitioning of m into
substrings such that:

* A substring of m is a sequence of zero or more contiguous
characters and each character of m is part of exactly one substring.
* If the nth substring specifier is an underscore, the nth substring
of m is any single character.
* If the nth substring specifier is a percent sign, the nth
substring of m is any sequence of zero or more characters.
* If the nth substring specifier is neither an underscore nor a
percent sign, the nth substring of m is equal to that substring
specifier and has the same length as that substring specifier.
* The number of substrings of m is the same as the number of
substring specifiers.

Thus, if p is an empty string and m is not an empty string, the result
is false. Similarly, it follows that if m is an empty string and p is
not an empty string (except for a string containing only percent signs),
the result is false.

The predicate m NOT LIKE p is equivalent to the search condition NOT (m
LIKE p).
You may also consider using '_' instead of or in addition to '%' in LIKE
predicate pattern.

Jan M. Nelken
--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jun 27 '08 #6

P: n/a
Gregor KovaŤ wrote:
So why SQL-s:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
and
SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'
where I set ? to '' or even NULL

don't produce same results?
Kovi,

Teh question mark is just that: A questionmark.
Just as in: WHERE c1 = 'HELLO?"

So what you want is:
LIKE '%' || CAST(? AS VARCHAR(100)) || '%'

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '08 #7

P: n/a
Great, this worked, thanks.

Best regards,
Kovi

Serge Rielau pravi:
Gregor KovaŤ wrote:
>So why SQL-s:
SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%'
and
SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%'
where I set ? to '' or even NULL

don't produce same results?
Kovi,

Teh question mark is just that: A questionmark.
Just as in: WHERE c1 = 'HELLO?"

So what you want is:
LIKE '%' || CAST(? AS VARCHAR(100)) || '%'

Cheers
Serge
--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jun 27 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.