Beschreibung/Description

Beispielverwendung/Example how to use

Ergebnis/Result

T-SQL Definition

Beschreibung/Description

Einfache SQL-Funktion zum Enternen von Text links/rechts.

A simple SQL function to remove text left/right.

Beispielverwendung/Example how to use

-- Assume you want to remove the text "Line 3." from the right side of the string

SELECT N'"' + [dbo].[gsSAG_Remove_LeftOrRight] ( N'I am the example text. Line 2. Line 3.' , N'Line 3.', 0, 1 ) + N'"'

Ergebnis/Result

T-SQL Definition

IF EXISTS ( SELECT TOP 1 [Name] FROM dbo.sysobjects WITH(Readuncommitted) 
            WHERE id = OBJECT_ID(N'[dbo].[gsSAG_Remove_LeftOrRight]')
			AND xtype in (N'FN', N'IF', N'TF') )
BEGIN

  DROP FUNCTION [dbo].[gsSAG_Remove_LeftOrRight]

END

GO

CREATE FUNCTION [dbo].[gsSAG_Remove_LeftOrRight]
( 
@sExp nvarchar(max)
, @sFind nvarchar(max)
, @bAtBeginning bit
, @bRemoveAll bit 
)
RETURNS nvarchar(max) AS  

-- Purpose:
-- Removes the search text from @sExp at the beginning or at the end.

-- !!! 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:
--
-- @sExp =
--   Mandatory: The String to parse.
--
-- @sFind =
--   Mandatory: Search string.
--
-- @bAtBeginning =
--   1 = replaces at the beginning.
--   0 = replaces at the end.
--
-- @bRemoveAll =
--   1 = replaces all found occurrences of @sFind left or right.
--   0 = replaces only the first found occurrence of @sFind left or right.

BEGIN 

  declare @sResult as nvarchar(max)

  set @sResult = @sExp

  set @sExp = ISNULL(@sExp, N'')

  IF @sExp = N'' RETURN @sExp

  set @sFind = ISNULL(@sFind, N'')

  IF @sFind = N'' RETURN @sExp
  
  IF CHARINDEX(@sFind, @sExp) = 0 RETURN @sExp

  set @bAtBeginning = ISNULL(@bAtBeginning, 0)

  set @bRemoveAll = ISNULL(@bRemoveAll, 0)

  IF @bAtBeginning <> 0 
  BEGIN
    
    WHILE (SUBSTRING(@sExp, 1, LEN(@sFind)) = @sFind) 
    BEGIN

      SET @sExp = SUBSTRING(@sExp, LEN(@sFind) + 1, LEN(@sExp))
      
      IF @bRemoveAll = 0 BREAK
      
    END
    
  END
  ELSE
  BEGIN

    WHILE (SUBSTRING(@sExp, LEN(@sExp)-LEN(@sFind) + 1, LEN(@sFind)) = @sFind) 
    BEGIN
    
      SET @sExp = SUBSTRING(@sExp, 1, LEN(@sExp)-LEN(@sFind))

      IF @bRemoveAll = 0 BREAK
      
    END
      
  END
  
  SET @sResult = @sExp
  
  RETURN @sResult

END

GO