CODEWITHSUNDEEP
sqlsql-server
Basharat Ali
Basharat Ali

Reputation: 1209

Like function in SQL Server returned all values from the table if it null

I am trying to retrieve data from the database using the like operator. The problem is that if the inner query returns null/empty then the outer query retrieve all values from the table.

SELECT customer_ref AS customer_Ref, pname AS company_name 
FROM BSD_Main_Evamp_JBW t 
WHERE  t.pname LIKE CONCAT( '%', (SELECT TOP 1 pname 
                                  FROM BSD_Main_Evamp_JBW 
                                  WHERE customer_ref = '1-31898332401005'
                                 ), '%' 
                          )

This kind of query work perfectly in MySQL means if inner query return null then outer query also return null.

I tried isnull function also with this query, but issue not resolved. How can I fix this query for SQL Server so if the inner part returns null then the outer part also returns null.

Upvotes: 0

Views: 1104

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

To be honest, I think the more elegant solution is to move the concat() to the subquery:

WHERE t.pname LIKE (SELECT TOP 1 CONCAT('%', pname, '%') 
                    FROM BSD_Main_Evamp_JBW 
                    WHERE customer_ref = '1-31898332401005'
                   )

CONCAT() is a convenient function, because it will convert numbers to strings. + will return an error if pname is not a string.

The problem with your code is that CONCAT() is NULL-friendly. That is, it ignores NULL arguments rather than returning NULL.

With this version, the subquery still returns NULL if there is no match. However, there is no CONCAT() to turn this into a valid string, so the LIKE does not return true.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

CONCAT() will return %% if sub-query return null value, try to add + :

. . .
WHERE t.pname LIKE '%' + <subquery here> + '%'

However, you can use variable instead of sub-query : 

DECLARE @pname VARCHAR(255)

SELECT TOP (1) @pname = pname
FROM BSD_Main_Evamp_JBW 
WHERE customer_ref = '1-31898332401005'

SELECT . . 
FROM BSD_Main_Evamp_JBW t
WHERE (@pname IS NOT NULL AND t.pname LIKE '%' + @pname + '%');

Upvotes: 2

Related Questions