CODEWITHSUNDEEP
sqlsql-serversql-like
praveen
praveen

Reputation: 407

like operator issue in sql server

declare @CustField6 nvarchar(10)
declare @sample nvarchar(10)
set @CustField6 = 'sf'

select customer_id  from csu_customer_policy
    inner join csu_policy on csu_policy.policy_number = csu_customer_policy.policy_number
    and csu_policy.data_source = csu_customer_policy.data_source
    where csu_policy.vehicle_no like  + '''' + @CustField6 + '%''' and csu_customer_policy.delete_status = 0 and csu_customer_policy.site_id = 1

I want all the records who have csu_policy.vehicle_no starts with sf%

if I give the value sf directly instead of @CustField6 it gives all my records but when I execute this by using @CustField6 it does not give any error but retrieves no records, even I printed this code to check if the query is correctly being framed, and it is working fine. But I don't know why it is not retrieving any records?

Upvotes: 0

Views: 129

Answers (6)

Vikram Jain
Vikram Jain

Reputation: 5588

Please, use single quote as per below :

declare @CustField6 nvarchar(10)
declare @sample nvarchar(10)
set @CustField6 = 'sf'

select customer_id  from csu_customer_policy
    inner join csu_policy on csu_policy.policy_number = csu_customer_policy.policy_number
    and csu_policy.data_source = csu_customer_policy.data_source
    where csu_policy.vehicle_no like  '' + @CustField6 + '%' and csu_customer_policy.delete_status = 0 and csu_customer_policy.site_id = 1

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

Your syntax is incorrect. It should be:

where csu_policy.vehicle_no like @CustField6 + '%'

Assuming that @CustField6 is equal to sf, this is equivalent to 

where csu_policy.vehicle_no like 'sf%'

If you do this:

where csu_policy.vehicle_no like ''''  + @CustField6 + '%''''

Then you are actually doing this:

where csu_policy.vehicle_no like '''sf%'''

In other words, you're finding records that match 'sf%' - records that start with a quote, then sf, then anything, then end with a quote.

Upvotes: 2

user1613212
user1613212

Reputation: 93

Remove the unwanted quotes.. Try this.

declare @CustField6 nvarchar(10)
declare @sample nvarchar(10)
set @CustField6 = 'sf'

select customer_id  from csu_customer_policy
    inner join csu_policy on csu_policy.policy_number = csu_customer_policy.policy_number
    and csu_policy.data_source = csu_customer_policy.data_source
    where (csu_policy.vehicle_no like  + '' + @CustField6 + '%') and    
           csu_customer_policy.delete_status = 0 and 
           csu_customer_policy.site_id = 1

Upvotes: 0

Tan Suiwseng
Tan Suiwseng

Reputation: 77

Try get rid + 

from
 csu_policy.vehicle_no like + '''' + @CustField6 + '%'''

To
 csu_policy.vehicle_no like  ''''+ @CustField6 + '%'''

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

Change your criteria to:

WHERE csu_policy.vehicle_no LIKE @CustField6 + '%'

Demo: SQL Fiddle

Upvotes: 1

JanR
JanR

Reputation: 6132

I think the trouble with your query is that you have a few to many ' and + in your where condition: It should be where csu_policy.data_source like @custField6 + '%' instead of the csu_policy.vehicle_no like + '''' + @CustField6 + '%'''

declare @CustField6 nvarchar(10) 
declare @sample nvarchar(10) 
set @CustField6 = 'sf'

select customer_id 
from csu_customer_policy 
inner join csu_policy on csu_policy.policy_number = csu_customer_policy.policy_number 
      and csu_policy.data_source = csu_customer_policy.data_source 
where csu_policy.vehicle_no like @CustField6 + '%' 
and csu_customer_policy.delete_status = 0 and csu_customer_policy.site_id = 1

Upvotes: 0

Related Questions