代码之家 › 专栏 › 技术社区 › Mitesh Jain

将多个特殊字符替换为单个短划线

sql-server-2014 sql-server

Mitesh Jain · 技术社区 · 8 年前

嗨,我的数据是(围巾)/(围巾),我想把它显示为围巾,但我拿不到

我下面的代码是

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/durags/wrapps”应改为“bandanas-durags-wrapps”

“帽子和消音器”应改为“帽子和消音器”

2 回复 | 直到 8 年前

t-clausen.dk 8 年前

试试这个:

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)), ' ', '- '), ' -', ''), ' ', '')

Larnu 8 年前

我不想说这个解决方案很漂亮,它不是。它能完成这项工作,但我不指望它能快速工作。它利用了艾伦·伯斯坦的 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