none
Split Help in Scalar Function Input Parameter

    Question

  • Need Help with Split

    Hello I have function called average.It is having two input parameter like Date,Category.In my database I have different categores like Purchase,Sales,Debt,Advance...
    Now When user executes function for single category then It is working fine.Now sometimes there is a scenarion that sometimes user enters Category as 'Purchase,Sales' as input parameter of function

    Aso I need to handle them in my query where clause like

    Where Category in ('Purchase','Sales')

    So Currently it is considering it as

    Where Category in ('Purchase,Sales')


    I tried it with Charindex and get substring .But Do i need to use cursor to concate one by one string or is there any other way I can get
    Monday, August 19, 2013 7:19 AM

Answers

  • Use split function and use the output table in your query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 19, 2013 8:18 AM
  • As Latheesh said, below method can be applied,

    CREATE FUNCTION [dbo].[fnSplit](@sentence as NVARCHAR(500))
    RETURNS @temp table(part NVARCHAR(50))
    AS
    BEGIN
    	DECLARE @TmpWords NVARCHAR(500),@NewWord NVARCHAR(500)
    	DECLARE @splitBY nvarchar(2)
    	
    	SET @splitBY= ','
    	SET @TmpWords = replace(@sentence,'#','') + @splitBY
    	WHILE CHARINDEX(@splitBY, @TmpWords) > 0
    	BEGIN
    		SET @NewWord = LEFT(@TmpWords, CHARINDEX(@splitBY, @TmpWords))
    		SET @TmpWords = SUBSTRING(@TmpWords,CHARINDEX(@splitBY, @TmpWords)+1,len(@TmpWOrds))
    		insert into @temp values (rtrim(ltrim(replace(@NewWord,@splitBY,''))))
        END
    	RETURN 
    END
    ---------------to be implemented inside function
    DECLARE @TEMP TABLE(VALS VARCHAR(20))
    INSERT INTO @TEMP
    SELECT * FROM DBO.[fnSplit]('TESTING,TESTING1')
    SELECT * FROM TABLENAME WHERE COLNAME IN (SELECT VALS FROM @TEMP)


    Regards, RSingh


    Monday, August 19, 2013 8:25 AM

All replies

  • You need to implement as a dynamic sql.

    For example,

    declare @WhereCols varchar(200),@sql nvarchar(max)
    set @WhereCols = ''''+ replace('Purchase,Sales',',',''',''') + ''''
    set @sql = 'select * from tablename where columnname in (' + @WhereCols + ')'
    exec(@sql)


    Regards, RSingh

    Monday, August 19, 2013 7:37 AM
  • Hi,

    As you have to use it in Function, "Exec" won't be allowed in SQL function. Here, you can convert this CSV file to XML and then can have query to read from XML, which is much easy and simple. You can find reference on my blog, Read values from Comma Separated variable.

    Thanks,


    Tejas Shah
    SQL YOGA

    Monday, August 19, 2013 7:46 AM
  • MY categories are not hardcoded .They are coming from table
    Monday, August 19, 2013 7:46 AM
  • Use split function and use the output table in your query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 19, 2013 8:18 AM
  • As Latheesh said, below method can be applied,

    CREATE FUNCTION [dbo].[fnSplit](@sentence as NVARCHAR(500))
    RETURNS @temp table(part NVARCHAR(50))
    AS
    BEGIN
    	DECLARE @TmpWords NVARCHAR(500),@NewWord NVARCHAR(500)
    	DECLARE @splitBY nvarchar(2)
    	
    	SET @splitBY= ','
    	SET @TmpWords = replace(@sentence,'#','') + @splitBY
    	WHILE CHARINDEX(@splitBY, @TmpWords) > 0
    	BEGIN
    		SET @NewWord = LEFT(@TmpWords, CHARINDEX(@splitBY, @TmpWords))
    		SET @TmpWords = SUBSTRING(@TmpWords,CHARINDEX(@splitBY, @TmpWords)+1,len(@TmpWOrds))
    		insert into @temp values (rtrim(ltrim(replace(@NewWord,@splitBY,''))))
        END
    	RETURN 
    END
    ---------------to be implemented inside function
    DECLARE @TEMP TABLE(VALS VARCHAR(20))
    INSERT INTO @TEMP
    SELECT * FROM DBO.[fnSplit]('TESTING,TESTING1')
    SELECT * FROM TABLENAME WHERE COLNAME IN (SELECT VALS FROM @TEMP)


    Regards, RSingh


    Monday, August 19, 2013 8:25 AM