CODEWITHSUNDEEP
sqlms-accesssubstring
Kfir
Kfir

Reputation: 105

Search a substring using SQL in C# OleDb

I have a Customers Database in MS Access. I want to make a sql query that searches a substring from my primary key field.

For Example :

I want to select all the IDs that contain the number "35".

My DataBase before the SELECT statement :

enter image description here

My DataBase after the SELECT statement :

Example For a wanted substring

Is it possible ?

Upvotes: 0

Views: 1773

Answers (3)

Steve Lovell
Steve Lovell

Reputation: 2574

Since the Access wildcard is * and not % and, AFAIK, Access doesn't support CONTAINS I think you really want:

SELECT * FROM MyTable WHERE ID like "*35*"

Though based on our discussion in comments, other options (for when the ID field isn't a string) are:

SELECT * FROM MyTable WHERE Str(ID) like "*35*"

And

SELECT * FROM MyTable WHERE CStr(ID) like "*35*"

Upvotes: 2

Jake
Jake

Reputation: 604

SELECT *
FROM your_table
WHERE CAST(ID AS VARCHAR(20)) LIKE '%35%';

Upvotes: -1

Charles Bretana
Charles Bretana

Reputation: 146603

Assuming that the ID field is numeric,

In MsAccess:

Select * from myTable
where str(ID) like "*35*"

in SQL Server

Select * from myTable
where str(ID, 12, 0) like '%35%'

Upvotes: 0

Related Questions