如何在 SQL Server 中使用预定义的特殊字符修剪字符串?

问题描述

我想使用一些特殊字符来修剪 SQL Server 字符串，例如،,.?!؛,،,><=+ـ".

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

SQL 服务器的 ltrim 和 rtrim 函数只去除空格字符.

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

有没有人有实现 SpecialTrim 功能的想法?

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)

这篇关于如何在 SQL Server 中使用预定义的特殊字符修剪字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！