6

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?

htaghizadeh
  • 571
  • 6
  • 20
  • 2
    Are you looking for [**`REPLACE`**](https://msdn.microsoft.com/en-GB/library/ms186862.aspx), maybe? – Radu Gheorghiu Feb 18 '16 at 15:38
  • Using nested replace for this is the simplest and fastest way to deal with this. – Sean Lange Feb 18 '16 at 15:38
  • 1
    Do you mean just one of these characters, or any of them at the same time? – Marcus D Feb 18 '16 at 15:39
  • This should help you: http://stackoverflow.com/questions/7838676/sql-server-trim-character – ARZ Feb 18 '16 at 15:40
  • You can try creating a function which will parse each string in a loop and remove each of these characters. Mind you, this is a little more complicated. – Radu Gheorghiu Feb 18 '16 at 15:40
  • 1
    @ARZ Looks cool, but it would depend on the data. OP, can you post some sample data? – Radu Gheorghiu Feb 18 '16 at 15:40
  • This might be also of help to you: http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function – Ralph Feb 18 '16 at 15:41
  • @RaduGheorghiu replace would remove all instances not just those at the beginning and end. – Martin Smith Feb 18 '16 at 15:41
  • You could knock up a CLR function that uses the [String.Trim](https://msdn.microsoft.com/en-us/library/d4tt83f9(v=vs.110).aspx) method. – Martin Smith Feb 18 '16 at 15:43
  • CLR seems a bit heavy for something so simple. – Sean Lange Feb 18 '16 at 15:45
  • @SeanLange - Disagree - it will be trivial to write and out perform any TSQL method. – Martin Smith Feb 18 '16 at 15:46
  • @MartinSmith but CLR for some super basic replace is overkill. Sure it is simple to write it but it is like hunting flies with a bazooka. – Sean Lange Feb 18 '16 at 15:47
  • @SeanLange - So lets see the TSQL code that trims the desired characters from the beginning and end of `،,.?!؛,،,><=.the quick brown fox،,.?!؛,،,><=.jumped over the lazy dog،,.?!؛,،,><=.` and leaves the ones in the middle of the string alone. – Martin Smith Feb 18 '16 at 15:51
  • We still don't really know if the OP wants to remove all instances or just the beginning/trailing. For the example you posted then I would agree that CLR would be the simplest. – Sean Lange Feb 18 '16 at 15:54
  • Actually I have thought of a TSQL way. – Martin Smith Feb 18 '16 at 15:56

2 Answers2

5

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

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);
Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275