Search a substring using SQL in C# OleDb

Question

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 :

My DataBase after the SELECT statement :

Is it possible ?

but.. is your primary key a string or a number?? If it is a string you should check this link out: w3schools.com/sql/sql_like.asp — Leandro Ocampo
– Leandro Ocampo, Commented May 5, 2017 at 20:15

Steve Lovell · Accepted Answer · 2017-05-06 10:19:14Z

2

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*"

edited May 6, 2017 at 10:19

answered May 5, 2017 at 20:21

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kfir Over a year ago

can i use it in C# using OleDb?

Steve Lovell Over a year ago

I've never tried, but why not give it a go? If you run into problems, come back with another question.

Kfir Over a year ago

It's working for string field , how can i use it for integer field ?

Steve Lovell Over a year ago

Within Access it seemed to work for me against integer fields without changes. However, you might want to try WHERE str(ID) like "*35*".

Kfir Over a year ago

Thank You , But i need it for C# and it doesn't work with str(ID) , It doesn't know what is str.

|

Charles Bretana · Accepted Answer · 2017-05-05 20:18:03Z

0

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%'

answered May 5, 2017 at 20:18

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

1 Comment

Kfir Over a year ago

can i use it in C# using OleDb?

Jake · Accepted Answer · 2017-05-05 20:19:53Z

-1

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

answered May 5, 2017 at 20:19

Jake

6143 gold badges11 silver badges39 bronze badges

2 Comments

Steve Lovell Over a year ago

I didn't downvote, but question was about MS Access. I don't think your code is valid there. For starters, the wildcard is * not %. As for the CAST ... ?

Jake Over a year ago

Ah, I didn't notice the MS Access tag. I jumped the gun there.

Collectives™ on Stack Overflow

Search a substring using SQL in C# OleDb

3 Answers 3

8 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related