Get all or part result from sql using one TSQL commnd

Question

Hi All

Here is my condition. There is a Text box in a form, if you don't input any thing, it would return all rows in this table. If you input something, it would return rows whose Col1 matches the input. I try to use the sql below to do it. But there is one problem, these columns allows Null value. It wouldn't return the row with NULL value. Is there any way to return all or matched row based on the input?

Update

I use the ObjectDataSource and ControlParameter to pass the parameter, when the input of control is empty, the ObjectDataSource would pass a DBNULL to the TSQL commnd.

Col1   Col2   Col3
ABCD   EDFR   NULL
NULL   YUYY   TTTT
NULL   KKKK   DDDD


select * from TABLE where Col1 like Coalesce('%'+@Col1Val+'%',[Col1])

Answer 1

Something like this might work.

select * from TABLE where Coalesce(Col1,'xxx') like Coalesce('%'+@Col1Val+'%',Col1, 'xxx')

Answer 2

Have you tried

SELECT   * 
FROM     TABLE 
WHERE    COALESCE(Col1, '') LIKE COALESCE ('%'+@Col1Val+'%', [Col1]) 

Answer 3

SELECT * FROM [TABLE]
WHERE (Col1 LIKE '%'+@Col1Val+'%' OR (@Col1Val = '' AND Col1 IS NULL))

Answer 4

Use this:

SELECT   * 
FROM     TABLE 
WHERE    
    ( @Col1Value IS NOT NULL AND COALESCE(Col1, '') LIKE '%' + @Col1Val +'%' )
    OR @Col1Value IS NULL

Or perhaps use this, so null won't creep in to your query:

string nullFilteredOutFromQueryString = @"SELECT   * 
FROM     TABLE 
WHERE    
    ( @Col1Value <> '' AND COALESCE(Col1, '') LIKE '%' + @Col1Val +'%' )
    OR @Col1Value = ''";


var da = new SqlDataAdapater(nullFilteredOutFromQueryString, c);
// add the TextBox's Text property directly to parameter's value.
// this way, null won't creep in to your query
da.SelectCommand.Parameters.AddWithValue("Col1Value", txtBox1.Text); 
da.Fill(ds, "tbl");