Home > Software design >  Decoding a sql function
Decoding a sql function

Time:10-29

I'm working with some queries that someone has written but has now left the organisation.

To begin with, here's a table-valued function - could someone please help me understand what its trying to do.

ALTER FUNCTION [dbo].[udfSplitString]
(    
    @Input NVARCHAR(MAX),
    @Character CHAR(1)
)
RETURNS @Output TABLE (
    Item NVARCHAR(1000)
)
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT
 
    SET @StartIndex = 1
    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
    BEGIN
        SET @Input = @Input   @Character
    END
 
    WHILE CHARINDEX(@Character, @Input) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Character, @Input)
         
        INSERT INTO @Output(Item)
        SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
         
        SET @Input = SUBSTRING(@Input, @EndIndex   1, LEN(@Input))
    END
 
    RETURN
END

Thanks

CodePudding user response:

A table-valued function that divides a text into substring rows depending on a separator character. Returns a single-column table with substrings as rows.

Value is the name of the column. If any of the input parameters are nvarchar or nchar, this function returns nvarchar. Otherwise, varchar is returned. The return type's length is the same as the string argument's length. For Example:

enter image description here

Hence, as metioned by Paul : Find all the function calls to see what is being passed.

STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT outputs a single-column table whose rows contain the substrings. The name of the output column is value.

  • Related