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?
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)
From SQL Server vNext and on you could use built-in TRIM function:
TRIM
Removes white spaces or specified characters from a string.
TRIM ( [ characters FROM ] string )Returns a character expression with a type of string argument where white spaces or specified characters are removed from both sides. Returns NULL if input string is NULL.
In your case:
DECLARE @Str NVARCHAR(100) = N',,,,,!؛Computation+Time, Cost،,.?!؛,،,><=+ـ';
SELECT TRIM(N'،,.?!؛,،,><=+ـ' FROM @Str);
REPLACE, maybe?