Solved

Problem with a LIKE clause  with a "*" inside in VB.Net accessing a DB2/400 table

Posted on 2016-10-07
7
108 Views
Last Modified: 2016-11-04
Hi

I get an error with a SQL statement getting data in a DB2/400 database when the WHERE clause contains a LIKE clause where the content of the LIKE includes the star character, "*", and this in a VB.Net application.
When my SELECT statement looks like this:

SELECT ... FROM MyTable WHERE Field1 LIKE 'ABC* %'

i.e. when the content of the like clause includes a star character.  The content of the LIKE clause is actually the value of  a field from an other table, so I do not control the presence or absence of the star character.

I get the error: "Error in Like operator: the string pattern 'ABC* %' is invalid."

The connection is done over IBM's own ODBC driver for the iSeries.

I *guess* it's because the star character is the wild card character in the Microsoft world, and the ODBC driver gets confused with 2 wild characters, maybe ? This is not an IBM DB2 error message, therefore probably a Microsoft error message.

Has anybody had this combination and knows how to avoid the error ?

Thanks for help on this
Bernard
0
Comment
Question by:bthouin
7 Comments
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 41833720
Please show your code.

Not a fix but a workaround: Field1 LIKE 'ABC* %'  is functionally equivalent to LEFT(Field1,4) = 'ABC* '
0
 
LVL 16

Expert Comment

by:John Tsioumpris
ID: 41833721
Since you are using ODBC you could do a test by using Ms Access(if you know/have) to establish connection to your DB2 and perform the query (pass through)
0
 
LVL 1

Author Comment

by:bthouin
ID: 41833770
@Gary
My code:

Dim dsCT As New DataSet
Dim selRow as DataRow()
selRow = dsCT.Tables("MyTable").Select("Fild1 = 'Bla' " & _
                                                                              "AND Field2 LIKE '" & MyTextWithStar & " %' " & _
                                                                              "AND Field3 = 'Blo'")

Open in new window


if MyTextWithStar contains a "*", I get the error. If there is no star, it works fine.

@John
I frankly don't see how Access is going to help. I'm developing also with Access and VBA, and I am accessing the same tables in the DB2/400 with Access, so that's not a problem to try, but what's the goal ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 250 total points
ID: 41833832
Can you please check if this applies to your situation .
0
 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
ID: 41833926
John Tsioumpris essentially has what could be the answer. You should try enclosing the asterisk in square brackets. However, that probably requires validating any MyTextWithStar value to see if it contains an asterisk (or a percent sign, "%", since both have special meaning in a LIKE clause in your case).

You'll want to try replacing every embedded instance of "*" (or "%") with "[*]" (or "[%]") in MyTextWithStar before concatenating it into your LIKE clause.
0
 
LVL 16

Expert Comment

by:John Tsioumpris
ID: 41833929
There is probably an issue with in between characters (the * char)  thats why i pointed the Ms Article in order to be precise...
0
 
LVL 1

Author Comment

by:bthouin
ID: 41836772
Ah, OK, I can easily enclose the * in square brackets as I already detect it and replace it with nothing for the moment to avoid the error. Let's see what happens then.

And to be honest, the MS article is so longwinded and precise that even if I had looked at it (how should I know that I should search for data column expressiion ?..), I would have given up looking long before finding the one reference to the bracket stuff...

Thanks, and I'll revert as soon as I can test the change.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question