in my select query shows errors

Question

i create one function string split. i am calling that function in below select query. My function name is dbo.StringSplits SELECT [dbo.StringSplits](A.farm_detailsdata),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 WHERE B.farmerseason = '2018' When i execute the above query shows error as follows Incorrect syntax near 'A'. what is mistake in my above query. please help me.

Rajeev Punhani · Answer

Hi Narasiman , You are getting this error because you are using double single quotes for the varchar field and it is clear from error that you can not apply subtraction on varchar. Instead of using double single quotes ,using single quotes once where ever you have used them. replace ''-''(used 3 places in your query screen shot) with '-' '''' with ''

Nitin Sontakke · Answer

I am not able to open the .rar file. I tried with 7Zip and WinZip. You can even use 'insert image' function. Second button from right on first row of buttons in reply editor.

Narasiman nar · Answer

i send the screen shot of my error. Please find the attachment and let me know.

Nitin Sontakke · Answer

Please paste the screen shot of SQL Server Management Studio with BOTH the query and the error.

Narasiman nar · Answer

i also changed the function name as [dbo].[string_split] still i am getting error in my below query. 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.

Nitin Sontakke · Answer

The error is coming from your function. I still don't understand why you are not using function provided by Microsoft SQL Server itself. I have already provided link to you. Just change function name to [dbo].[string_split].

Narasiman nar · Answer

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. what is mistake in my above query. please help me.

Rajeev Punhani · Answer

Hi Narasiman, What you are trying to achieve is that you have a tabled value function that return multiple rows when you call a function that splits column values seperated by a seperator. Please try the below query. 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'

Nitin Sontakke · Answer

In that case, it has both built in features, string_split and json parsing. You don't have to develop your own solutions. Please go through following links: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

Nitin Sontakke · Answer

On another thread, I had asked you, which version of SQL Server are you using? and you didn't respond.

Nitin Sontakke · Answer

Two things: First, you have made a typing mistake, you should type [dbo].[StringSplits] but you have typed [dbo.StringSplits]. Can you see the difference after the word dbo? Second, even if you modify that, you will still not be able to execute that query because you are mixing a table returning function with another select statement which is not allowed. Just try to visualize how SQL Server should show you the result and you will know what i mean. Are you with me?

Narasiman nar · Answer

string split is function name String split function as follows Create FUNCTION [dbo].[StringSplits](@input NVARCHAR(MAX), @delimiter CHAR(1)=',') RETURNS @returnTable TABLE(item NVARCHAR(100)) AS BEGIN IF @input IS NULL RETURN; DECLARE @currentStartIndex INT, @currentEndIndex INT,@length INT; SET @length=LEN(@input); SET @currentStartIndex=1; SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex); WHILE (@currentEndIndex 0) BEGIN INSERT INTO @returnTable VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @currentEndIndex-@currentStartIndex))) SET @currentStartIndex=@currentEndIndex+1; SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex); END IF (@currentStartIndex INSERT INTO @returnTable VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @length-@currentStartIndex+1))); RETURN; END; SELECT [dbo.StringSplits](A.farm_detailsdata),A.farmer_id FROM dbo.tbl_farmer_farmdetails_mozambique A INNER JOIN dbo.tbl_farmerregistration_mozambique B ON B.farmerctscode = A.farmer_id when i run shows error as follows [dbo.StringSplits] is not recognized built in function name please help me how to solve this error. in my above select what is the mistake i made.

Nitin Sontakke · Answer

Have you supplied all the parameters for your function? Is your function a table returning or a scalar one? You cannot mix table returning function and a SELECT clause. For example, if your function is table returning, you cannot use it as above. In above select statement, only a scalar function is allowed. If you still have questions, please do let us know.

Narasiman nar · Answer

sql server version 2017 sql server managment studio

Rajeev Punhani · Answer

Hi Narasiman, I presume that you are having a function StringSplits and you have a column in tbl_farmer_farmdetails which has a column farm_detailsdata which needs to be splitted with a particular character but you are not passing any seperator .This issue is due to StringSplits only .Could you confirm what is this StringSplits.

in my select query shows errors

Answers (15)