SQL server String comma-separated function sharing, SQL server

SQL server String comma-separated function sharing, SQL server

After SQL-Function is created, the output results of the Function database are separated by commas (,). During Development, many parameters with comma (,) are passed in as parameters (this is not recommended when data is large)

For example, search for data with the name "Zhang San, Li 'er" and process this parameter in the database.

The function code is as follows:

CREATE FUNCTION [dbo].[fnSplitStr] ( @sText  NVARCHAR(Max), @sDelim  CHAR(1))RETURNS @retArray TABLE ( value VARCHAR(100))ASBEGIN DECLARE   @posStart  BIGINT,  @posNext  BIGINT,  @valLen   BIGINT,  @sValue   NVARCHAR(100); IF @sDelim IS NULL  BEGIN  IF LEN(@sText)>100 SET @sText = SUBSTRING(@sText, 1, 100)    INSERT @retArray (value)  VALUES (@sText); END ELSE BEGIN  SET @posStart = 1;  WHILE @posStart <= LEN(@sText)  BEGIN   SET @posNext = CHARINDEX(@sDelim, @sText, @posStart);   IF @posNext <= 0     SET @valLen = LEN(@sText) - @posStart + 1;   ELSE    SET @valLen = @posNext - @posStart;   SET @sValue = SUBSTRING(@sText, @posStart, @valLen);   SET @posStart = @posStart + @valLen + 1;   IF LEN(@sValue) > 0   BEGIN    IF LEN(@sValue)>100 SET @sValue = SUBSTRING(@sValue, 1, 100)        INSERT @retArray (value)    VALUES (@sValue);   END  END END RETURNEND

Now, we will introduce the SQL string comma-separated functions. You can refer to them.