Search code examples
sqlsql-serverstringtrim

How to trim string using predefined special characters in SQL Server?

I want to trim SQL Server strings using some special characters such as "،,.?!؛,،,><=+ـ".

The SQL server ltrim and rtrim functions only strips the space characters.

DECLARE @Str NVARCHAR(100) = N',,,,,!؛Computation+Time, Cost،,.?!؛,،,><=+ـ'
SELECT dbo.SpecialTrim(@Str, N'،,.?!؛,،,><=+ـ')

The result : Computation+Time, Cost

Have anyone any ideas in order to implement SpecialTrim function?

Solution

  • The below hardcodes the pattern.

    It looks for the first character that is not one of the characters to exclude at both ends.

    To make it dynamic you could build up the set of characters using string concatenation (be careful of characters containing special meaning in the pattern syntax)

    WITH T(String) AS
(
SELECT 'Computation+Time, Cost،,.?!؛,،,><=+ـ' union all
SELECT ',,,,,!؛Computation+Time, Cost،,.?!؛,،,><=+ـ' union all
SELECT 'Computation+Time, Cost،,.?!؛,،,><=+ـ' union all
SELECT 'Computation+Time, Cost' union all
SELECT ''
)
SELECT SUBSTRING(String,Start,len(String) + 2 - Start - Finish)
FROM T
CROSS APPLY
(
SELECT  PATINDEX('%[^،,.?!؛,،,><=+ـ]%' COLLATE Latin1_General_Bin,String),
        PATINDEX('%[^،,.?!؛,،,><=+ـ]%' COLLATE Latin1_General_Bin,REVERSE(String))
)ca(Start, Finish)