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

Question

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

Bineesh VP · Answer

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 Gupta · Answer

@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 VP · Answer

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 officemobile 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 strCustomerIDstrCompanyNamestrAccNoround ( cast (tblCustomer.strClaimAmount AS DECIMAL (102))2) as Acmount strClientNamestrNationality strPassportNostrLoanStatusstrOthersstrEmployerName 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 Gupta · Answer

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 VP · Answer

Dear Amit The your SQL SP code is not filtering the search criteria. it showing all the data regardless of search keyword.

Amit Gupta · Answer

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 P · Answer

Following link will give some idea. Link:https://solutioncenter.apexsql.com/quickly-search-for-sql-database-data-and-objects/