SQL search strings every possible way

39 views Asked by At

Im using a DataSet with the Table 'Orders'. There i have the TableAdapter GetDataByCustomerID() which containts this SQL:

SELECT        OrderID, CustomerID, EmployeeID
FROM            Orders
WHERE        (CustomerID LIKE @CustomerID)

Later im giving the Method the parameter 'Vin' for example. To find every Order with the Customer ID "VINET".

The problem is i cant get VINET by using just the string 'Vin'.

I tried:

 WHERE        (CustomerID LIKE '%Vin%')

I know this would work

 WHERE        (CustomerID LIKE 'Vin%')

but i want to be able to get the order if it containts vin. And the command 'contains' seems not to be working.

1

There are 1 answers

1
Gordon Linoff On

You seem to need a case-insensitive search. It is not clear what database you are using, but one method is:

where lower(CustomerID) like '%vin%'

Depending on your database, you can also set collation explicitly to avoid case issues. Some databases also support explicit case-independent comparison.