Parameter markers in LIKE clause

[=?ISO-8859-2?Q?Gregor_Kova=E8?=]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[jefftyzzer]

Re: Parameter markers in LIKE clause

On Jun 17, 5:29 am, Gregor Kovaè <gregor.ko...@m ikropis.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

[=?ISO-8859-2?Q?Gregor_Kova=E8?=]

Re: Parameter markers in LIKE clause

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...@m ikropis.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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[jefftyzzer]

Re: Parameter markers in LIKE clause

On Jun 17, 10:19 pm, Gregor Kovač <gregor.ko...@m ikropis.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...@m ikropis.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

[Jan M. Nelken]

Re: Parameter markers in LIKE clause

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

[=?ISO-8859-2?Q?Gregor_Kova=E8?=]

Re: Parameter markers in LIKE clause

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Re: Parameter markers in LIKE clause

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

[=?ISO-8859-2?Q?Gregor_Kova=E8?=]

Re: Parameter markers in LIKE clause

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-