将多个特殊字符替换为单个破折号

Question

嗨，我的数据是(围巾。)/(包装)，我想把它显示为围巾包装，但我无法得到它。

我下面的代码如下

DECLARE @str VARCHAR(400)
DECLARE @specialchars VARCHAR(50) = '%[/,~,@,#,$,%,&,*,(,),.,!^?:]%'
--SET @str = 'KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.BigGrin'
SET @str = '(SCARF.) / (WRAPS)'
SET @str = REPLACE(@str,'.','')
WHILE PATINDEX( @specialchars, @str ) > 0
SET @str = REPLACE(REPLACE( @str, SUBSTRING( @str, PATINDEX( @specialchars, @str ), 1 ),'-'),'-','-')

IF(LEFT(@str, 1) = '-')
   set @str = RIGHT(@str, LEN(@str) - 1)
IF(RIGHT(@str, 1) = '-')
   set @str = LEFT(@str, LEN(@str) - 1)

SELECT REPLACE(@str,' ','-')

但是它给出的数据是围巾--包装而不是围巾--包装。

我有像多重这样的数据类型，我想把它们转换成单个破折号。

例如：

“护肤美容供应”应改为“护肤美容供应”。 “BANDANAS/榴弹炮/包装”应改为“BANDANAS/BANDANAS/WRAPS” “HATS&消声器”应该改为“帽子-消声器”

Answer 1

试试这个：

DECLARE @str VARCHAR(400)='(SCARF.) / (WRAPS)'
DECLARE @specialchars VARCHAR(50) = '%[/,~,@,#,$,%,&,*,(,),.,!^?:-]%'

WHILE PATINDEX( @specialchars, @str ) > 0
SET @str = REPLACE( @str, SUBSTRING( @str, PATINDEX( @specialchars, @str ), 1 ),' ')

SELECT REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(@str)), ' ', '- '), ' -', ''), ' ', '')

Answer 2

我不会说这个解决方案很漂亮；它不是。它能完成这项工作，但我不指望它能很快奏效。它利用了Alan的NGrams8k，因此您也需要确保服务器上有一个副本。

如果您不理解它，可以自由地问(除了NGrams8K是如何工作的，这就是本文的目的)：

CREATE TABLE T (string varchar(400));
INSERT INTO T
VALUES ('KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.BigGrin'),
       ('(SCARF.) / (WRAPS)');
GO
WITH Replacements AS (
    SELECT *,
           CASE WHEN NG.token NOT LIKE '[ /,~@#$%&*().!^?:]' THEN NG.token ELSE '-' END AS TokenR
    FROM T
         CROSS APPLY dbo.NGrams8k ('-'+T.String+'-',1) NG),
Repeating AS (
    SELECT *,
           CASE WHEN TokenR = '-' AND TokenR = LAG(TokenR) OVER (PARTITION BY string ORDER BY position) THEN NULL ELSE TokenR END AS TokenRR
    FROM Replacements),
Cleaned AS (
    SELECT string,
           STUFF((SELECT sq.TokenRR + ''
                  FROM Repeating sq
                  WHERE sq.TokenRR IS NOT NULL
                    AND sq.string = R.string
                  ORDER BY position
                  FOR XML PATH('')),1,1,'') AS CleanString
    FROM Repeating R
    GROUP BY string)
SELECT string, LEFT(CleanString, LEN(CleanString)-1) AS CleanedString
FROM Cleaned;

GO
DROP TABLE T;

通过提供示例数据，您可以得到以下输出：

string                                     | CleanedString
-------------------------------------------|---------------------------------
(SCARF.) / (WRAPS)                         | SCARF-WRAPS
KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.BigGrin  | KRA-NTHI-KUMAR-KU-MAR-GO-U-BigGrin