How to include null sql records with like %

Question

0.00/5 (No votes)

See more:

Hi All,

I have created an sql command with a where clause

where PLPRLC like @PLPRLC

and then the following parameter with option of all records

SQL

If ddlLocation.Text = "" Then
           daProductionMachines.SelectCommand.Parameters.Add(New SqlParameter("@PLPRLC", "%"))
       Else
           daProductionMachines.SelectCommand.Parameters.Add(New SqlParameter("@PLPRLC", ddlLocation.Text))
       End If

The problem is with the Null records which are not picked up in either case.
I want that in case of ddllocation.text="", all records should be fetched including Null.

Pls suggest.
Thanks
Atul

Posted 26-Feb-15 19:06pm

atul sharma 5126

Add a Solution

3 solutions

Solution 1

You need manipulate the query string. I assume your query something like this:

SQL

'SELECT * FROM members WHERE code LIKE @PLPRLC'

into

SQL

'SELECT * FROM members WHERE IFNULL(code, '') LIKE @PLPRLC'

Please refer to your SQL engine documentation. Query above should work in SQL Server or MySQL

Posted 26-Feb-15 19:17pm

hadi_rena

Comments

atul sharma 5126 27-Feb-15 1:56am

Thanks for the reply!
Message: 'IFNULL' is not recognized built-in function name

used is sql server 2012

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11484114 · Accepted Answer · 2015-02-26T19:48:00

Solution 2

Use the query like the following one...

select * from table_name where PLPRLC is null or PLPRLC like @PLPRLC

Posted 26-Feb-15 19:48pm

Member 11484114

Comments

atul sharma 5126 27-Feb-15 1:58am

Thanks for your reply dear!

But I have to specify the value of PLPRLC from a parameter based upon a drop down list.

If "All" is selected in the ddl then all records including Null are desired to be shown else only the selected ddl value records are to be shown.

Problem is when All is selected then Null records are not picked up..

Member 11484114 27-Feb-15 2:11am

Check by using this query,

select * from table_name where (@PLPRLC='All') or (PLPRLC like @PLPRLC)

here, if u pass the parameter @PLPRLC as 'All', you will get all the records including
null or you will get only the records that is @PLPRLC (not null records also)

atul sharma 5126 27-Feb-15 2:33am

Thanks a lot. It worked perfectly.

Member 11484114 · Accepted Answer · 2015-02-26T20:13:00

Solution 3

Check by using this query,

select * from table_name where (@PLPRLC='All') or (PLPRLC like @PLPRLC)

here, if u pass the parameter @PLPRLC as 'All', you will get all the records including
null or you will get only the records that is @PLPRLC (not null records also)

Posted 26-Feb-15 20:13pm

Member 11484114