How to Convert the db2 TRIM function into SQL Server

Question

In the db2 database:

select trim(T '0' from '00a0Bjifea00a000') 
from sysibm.sysdummy1;

I can get the result: 00a0Bjifea00a

But how can I get the same result in the SQL Server database?

Answer 1

The best function that I can think about it can be something like this ^[source], that will support DB2 functionality:

CREATE FUNCTION dbo.DB2Trim
(
    @Side NVARCHAR(10),
    @CharacterToTrim NVARCHAR(1),
    @Value NVARCHAR(4000)

)
RETURNS NVARCHAR(4000)
AS
BEGIN
    IF (UPPER(@Side) IN ('L','B','','LEADING','LEFT','BOTH'))
        SET @Value = REVERSE(SUBSTRING(@Value, PATINDEX('%[^'+@CharacterToTrim+']%', @Value), LEN(@Value)));
    ELSE
        SET @Value = REVERSE(@Value);
    IF (UPPER(@Side) IN ('T','R','B','','TRAILING','RIGHT','BOTH')) 
        SET @Value = REVERSE(SUBSTRING(@Value, PATINDEX('%[^'+@CharacterToTrim+']%', @Value), LEN(@Value)));
    ELSE
        SET @Value = REVERSE(@Value);
    RETURN @Value
END
GO

Usage:

SELECT dbo.DB2Trim('T','0', '00a0Bjifea00a000');

Answer 2

CREATE FUNCTION [dbo].[TRIM_T](@STR1  NVARCHAR(MAX),@STR2  NVARCHAR(MAX))
returns  NVARCHAR(MAX)
AS
BEGIN
    DECLARE @len1  int
    DECLARE @len2 int
    DECLARE @start  int 

    IF isnull(@STR2,'')='' 
     begin 
     RETURN  @STR2
     end  
    set @len1=LEN(@STR1)
    set @len2=LEN(@STR2)
    IF @len2<@len1  
     BEGIN
      RETURN @STR2
     END 

    WHILE RIGHT(@STR2,@len1)=@STR1
    BEGIN
      set @STR2=STUFF(@STR2,LEN(@STR2)-@len1+1,@len1,'')
      set @len2=@len2-@len1
    END
    RETURN  @STR2
END

SELECT [dbo].[TRIM_T]('0','00a0Bjifea00a000')