How to search db value with 'or' condition sql server

Bineesh Viswanath

9y1.2kLearn iOS Programming

Hi Sir,

I am in a project in which I need to implement search criteria using OR condition.

means, I need to search different column values based upon one sql parameter.

Kindly tell me the below query to replace 'AND' condition with 'OR'

isnull(cast(tblCustomer.strMobile as nvarchar (20)),'') LIKE '%'+@asMobile+'%'

and

isnull(cast(tblCustomer.strPhoneOff as nvarchar (20)),'') LIKE '%'+@asMobile+'%' and

isnull(cast(tblCustomer.strPhoneOff1 as nvarchar (20)),'') LIKE '%'+@asMobile+'%' and

isnull(cast(tblCustomer.strPhoneRes as nvarchar (20)),'') LIKE '%'+@asMobile+'%'

for c# code , I given like this:-

cmd.Parameters.Add("@asstrMobile", SqlDbType.NVarChar).Value = tbxOfficeResMobile.Text.Trim();

cmd.Parameters.Add("@asPhoneOff", SqlDbType.NVarChar).Value = tbxOfficeResMobile.Text.Trim();

cmd.Parameters.Add("@asPhoneOff1", SqlDbType.NVarChar).Value = tbxOfficeResMobile.Text.Trim();

cmd.Parameters.Add("@asPhoneRes", SqlDbType.NVarChar).Value = tbxOfficeResMobile.Text.Trim();

This code is not working because of all parameter taking same value. I think I need to change the SQL

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bineesh Viswanath9y ago
Dear All,

The issue has been solved.

I wrote in sql query like this:-

Convert(nvarchar(30),tblCustomer.strPhoneOff1) +''+ Convert(nvarchar(30),tblCustomer.strPhoneRes)+''+ Convert(nvarchar(30),tblCustomer.strMobile) like '%'+@asOfficePhone + '%'
Amit Gupta9y ago
@Binesh

As already stated in my earlier post that no need to cast and check for null values, you only have to specify the column name to which you want to apply the filter.

Have you tried, what I had posted? Didn't you got the expected output?

Then please share your table structure and your query, you are applying, so that I can provide you the exact query.
Bineesh Viswanath9y ago
Dear Amit,

There are 10 textbox using to search in database table from winform.

only mobile number textbox need to act like search multiple columns like office,mobile, residense numbers.

All other search is filtering by exact values except this contact number filteration.

when I implemented concatenation , i getting an error:-

Operand data type text is invalid for add operator
SELECT strUserID, strCustomerID,strCompanyName,strAccNo,round (cast(tblCustomer.strClaimAmount AS DECIMAL(10,2)),2)as Acmount ,strClientName,strNationality, strPassportNo,strLoanStatus,strOthers,strEmployerName
FROM tblCustomer where
isnull(cast(tblCustomer.strCustomerID as varchar (20)),'') LIKE @asCustID + '%' AND
isnull(cast(tblCustomer.strNationality as varchar (20)),'') LIKE @asNationality + '%' AND
isnull(cast(tblCustomer.strPassportNo as varchar (20)),'') LIKE @asPassportNo + '%' AND
isnull(cast(tblCustomer.strCompanyName as varchar (200)),'') LIKE '%'+ @asCustName + '%' AND
--isnull(cast(tblCustomer.strMobile as varchar (20)),'') LIKE @asstrMobile + '%' AND
isnull(cast(tblCustomer.strMobile +tblCustomer.strPhoneOff + tblCustomer.strPhoneOff1+tblCustomer.strPhoneRes as varchar (20)),'') LIKE '%'+@asstrMobile + '%' AND
isnull(cast(tblCustomer.strFax as varchar (200)),'') LIKE '%'+ @asNewNumber + '%' AND
isnull(cast(tblCustomer.strEmail as varchar (20)),'') LIKE @asEmail + '%' AND
isnull(cast(tblCustomer.strOthers as varchar (20)),'') LIKE @asOthers + '%' AND
isnull(cast(tblCustomer.strEmployerName as varchar (20)),'') LIKE @asCompanyName + '%' AND
isnull(cast(tblCustomer.strAccNo as varchar (20)),'') LIKE @asAccNo + '%' AND
isnull(cast(tblCustomer.strClaimAmount as varchar (20)),'') LIKE @asOSAmnt + '%' AND
isnull(cast(tblCustomer.strAddress as varchar (20)),'') LIKE @asPBOX + '%' AND
tblCustomer.strStack='0'
and strTeam=@asTeam
Amit Gupta9y ago
This is because of % symbol, if it was used in suffix and prefix of the value then it will search whatever value contains in your @param, you can skip % and go for only @param, this will give more precise value
Bineesh Viswanath9y ago
Dear Amit,

The your SQL SP code is not filtering the search criteria.

it showing all the data regardless of search keyword.
Amit Gupta9y ago
You already know the answer as you stated in your question to use OR operator, right?

Please consider the following points
1. No need to check for null or conversion to nvarchar in your query
isnull(cast(tblCustomer.strMobile as nvarchar (20)),'') LIKE '%'+@asMobile+'%'

2. In code behind, pass only one parameter and it will use to various it in various column

3. Use OR operator instead of AND, so that it will show the result when matches to any column

The query should be like this
declare @param nvarchar
set @param = '2'

select * from tablename
where
ColumnName1 like '%'+@param +'%' or columnName2 like '%'+@param +'%'
You can add more column as required. Write the above query in your SP .
Do let me know, if it works for you
Prakashraj P9y ago
Following link will give some idea. Link:https://solutioncenter.apexsql.com/quickly-search-for-sql-database-data-and-objects/