Beschreibung/Description

Beispielverwendung/Example how to use

Ergebnis/Result

T-SQL Definition

Beschreibung/Description

Einfache SQL-Tabellenfunktion, die einen übergebenen String splittet und als einzelne Datensätze zurückliefert.

A simple SQL table function that splits a given string and returns it as individual data records.

Beispielverwendung/Example how to use

SELECT * FROM [dbo].[gtblSAG_Get_Text_Split] ( N'I am the first line.|Sep|I am the second line.' , N'|Sep|')

Ergebnis/Result

T-SQL Definition

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gtblSAG_Get_Text_Split]') and xtype in (N'FN', N'IF', N'TF'))
begin
  drop function [dbo].[gtblSAG_Get_Text_Split]
end

GO

CREATE FUNCTION [dbo].[gtblSAG_Get_Text_Split](
  @sText NVARCHAR(MAX),
  @sSep NVARCHAR(255)
) 
RETURNS @tblResult TABLE 
(
  Nr INT IDENTITY(1,1),
  TextPart NVARCHAR(MAX)
)
AS  

-- Purpose:
-- Splits the generated text into individual elements, separated by the search text.
-- This variant does not allow(!) spaces as separators.

-- !!! Important !!!
-- SQL functions generally scale very poorly in bulk queries and should therefore not(!) be used in performance-critical queries.
-- Example: If an SQL function is used for a field in an SQL select list, all actions may take place several times for each(!) data record in the result set.
-- Depending on the content/action of the SQL function, this can lead to extremely slow query behavior and unnecessary table scans.
-- Therefore, please only use SQL functions if absolutely necessary.

-- Parameters:
--
--  @sText = 
--    Mandatory: The text which should be parsed.
--
--  @sSep = 
--    Mandatory: The search text which should be used to split the text.
--
-- Returns:
-- Table with parsed string parts, separated by the search text.

BEGIN

  SET @sText = ISNULL(@sText, N'')
  
  IF LTRIM(RTRIM(@sText)) = N''
  BEGIN

    INSERT INTO @tblResult SELECT @sText
    
    RETURN    
  
  END

  SET @sSep = ISNULL(@sSep, N'')

  IF LTRIM(RTRIM(@sSep)) = N''
  BEGIN
  
    INSERT INTO @tblResult SELECT @sText

    RETURN    

  END

  DECLARE @ll INT = LEN(@sText) + 1, @ld INT = LEN(@sSep);
 
  WITH a AS
  (
  SELECT
  [start] = 1,
  [end]   = COALESCE(NULLIF(CHARINDEX(@sSep, @sText, 1), 0), @ll),
  [value] = SUBSTRING(@sText, 1, COALESCE(NULLIF(CHARINDEX(@sSep, @sText, 1), 0), @ll) - 1)
  UNION ALL
  SELECT
  [start] = CONVERT(INT, [end]) + @ld,
  [end]   = COALESCE(NULLIF(CHARINDEX(@sSep, @sText, [end] + @ld), 0), @ll),
  [value] = SUBSTRING(@sText, [end] + @ld, COALESCE(NULLIF(CHARINDEX(@sSep, @sText, [end] + @ld), 0), @ll)-[end]-@ld)
  FROM a
  WHERE [end] < @ll
  )
  INSERT @tblResult SELECT [value]
  FROM a
  --WHERE LEN([value]) > 0 -- MQ: No! Also empty values represent an information!
  OPTION (MAXRECURSION 0);
 
  RETURN;

END

GO