Error in my sql server query

Question

SELECT FARM.item,A.farmer_id, A.farmer_season,A.farm_visitno FROM dbo.tbl_farmer_farmdetails A INNER JOIN dbo.tbl_farmerregistration B ON B.farmerctscode = A.farmer_id CROSS APPLY [dbo].[StringSplits](A.farm_detailsdata,',') AS FARM WHERE B.farmerseason = '2018' When i run the above query shows error as follows Operand data type varchar is invalid for subtract operator. please let me know what is the mistake in my above sql query. i tried for several it shows the same error only. please help me.

Nilesh Shah · Answer

Hi your error is not in this query but it seems in the function [dbo].[StringSplits] did you independently test this function by supplying some string and does it work fine that way?

Shahriar Nikkhah · Answer

i think it is the second parameter in the function, wondering why is it like ',' check that section

Jayesh Vaghasiya · Answer

declare @tbl_farmer_farmdetails table (farmer_id int ,farmer_season varchar(64),farm_visitno int,farm_detailsdata varchar(512)) declare @tbl_farmerregistration table (farmerctscode int ,farmerseason varchar(64)) insert into @tbl_farmer_farmdetails values (1,'2015-1',1,'Rice,Wheat,Barley') insert into @tbl_farmer_farmdetails values (2,'2015-2',2,'Jowar,Bajra,Ragi,Maize') insert into @tbl_farmer_farmdetails values (3,'2018-1',3,'Tea,Coffee,Cocoa') insert into @tbl_farmer_farmdetails values (4,'2018-2',5,'Banana,Papya,Guava') insert into @tbl_farmer_farmdetails values (5,'2018-3',6,'Apple,Orange') insert into @tbl_farmerregistration values (1,'2015') insert into @tbl_farmerregistration values (2,'2015') insert into @tbl_farmerregistration values (3,'2018') insert into @tbl_farmerregistration values (4,'2018') insert into @tbl_farmerregistration values (5,'2018') SELECT FARM.Value,A.farmer_id, A.farmer_season,A.farm_visitno FROM @tbl_farmer_farmdetails A INNER JOIN @tbl_farmerregistration B ON B.farmerctscode = A.farmer_id CROSS APPLY [dbo].[Split](A.farm_detailsdata,',') AS FARM WHERE B.farmerseason = '2018'