代码之家 › 专栏 › 技术社区 › Markov

如何用replace函数对SQL update中的'字符进行转义?

sql-server-2008 tsql sql-server sql

Markov · 技术社区 · 6 年前

我的表格里有这些数据

  PartNumber  | IsValid
 BC.QT.000002'     0
'CP.AC.'000010'    0
'CP.AL.000013      0
'CP.A'L.000016'    0
'CP.AL.000024'     0

我要做的是把 ' 从PartNumber值,但如果该引号'位于值的中间,例如'CP.AC公司.'000010' 我只想去掉后缀和前缀 '

预期结果如下所示

 PartNumber | IsValid
BC.QT.000002     0
CP.AC.'000010    0
CP.AL.000013     0
CP.A'L.000016    0
CP.AL.000024     0

CREATE TABLE #Temp(
   PartNumber          VARCHAR(20)
  ,Test                VARCHAR(30)
);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''test''',NULL);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''te''st2''',NULL);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''test3''',NULL);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''tes''t5''',NULL);

Update #Temp
set partNumber = REPLACE(PartNumber,'''','')
Where len(partNumber ) - len(replace(partNumber , '''', '')) = 2

我需要一种删除前缀/后缀的方法 '

4 回复 | 直到 6 年前

Hector Montero 6 年前

这将删除所有的'从左和右。例如:“sample”将是sample

已编辑:如果零件号仅包含'

UPDATE [T]
SET  [PartNumber] =
    CASE
        WHEN [X].[Left] IS NULL OR [X].[Right] IS NULL
            THEN ''
        ELSE
            SUBSTRING
            (
                 LEFT([T].[PartNumber], LEN([T].[PartNumber]) - ISNULL([X].[Right] - 1, 0)) -- Remove all the ' from the right
                ,ISNULL([X].[Left], 1) -- remove all the ' from the left
                ,LEN([T].[PartNumber])
            )
    END
FROM #temp AS [T]
CROSS APPLY
(
    SELECT
         [Left] = NULLIF(PATINDEX('%[^'']%', [T].[PartNumber]), 0) -- get the first position of a character distinct of ' (from the left)
        ,[Right] = NULLIF(PATINDEX('%[^'']%', REVERSE([T].[PartNumber])), 0) -- get the last position of a character distinct of ' (from the right)
) AS [X];

Zohar Peled 6 年前

另一个即使内容可能包含空格也很安全的选项是用例:

UPDATE #Temp
SET partNumber = CASE WHEN partNumber LIKE '''%''' THEN -- starts and ends with an apostrophe
                    SUBSTRING(partNumber, 2, LEN(partNumber)-2) 
                 WHEN partNumber LIKE '''%' THEN -- starts with an apostrophe
                     RIGHT(partNumber, LEN(partNumber)-1) 
                 WHEN partNumber LIKE '%''' THEN -- ends with an apostrophe
                     LEFT(partNumber, LEN(partNumber)-1) 
                 ELSE  
                     partNumber 
                 END

缺点是,除了比Gordon的答案更麻烦之外,它只会删除第一个和最后一个撇号——如果你有一个类似的值 ''asdf' ,将更新为 'asdf .

Gordon Linoff 6 年前

PartNumber ,您可以使用:

replace(ltrim(rtrim(replace(partnumber, '''', ' '))), ' ', '''')

这通过使用trim函数来处理从字符串的开头和结尾删除字符的操作。唉,这些只适用于空格;因此需要replace将引号转换为空格。

HABO 6 年前

Left() 和 Right()

PartNumber 由单个撇号组成,即第一个和最后一个字符是

-- Sample data:
declare @Samples as Table ( PartNumber VarChar(16) );
insert into @Samples ( PartNumber ) values
  ( 'BC.QT.000002' ), ( '''CP.AC.''000010''' ),
  ( '''CP.AL.000013' ), ( '''CP.A''L.000016''' ),
  ( '''CP.AL.000024''' );

-- Play with it:
select PartNumber,
  Substring( PartNumber,
    case when Left( PartNumber, 1 ) = '''' then 2 else 1 end,
    Len( PartNumber ) - case
      when Left( PartNumber, 1 ) = '''' and Right( PartNumber, 1 ) = '''' then 2
      when Left( PartNumber, 1 ) = '''' or Right( PartNumber, 1 ) = '''' then 1
      else 0 end ) as TrimmedPartNumber
  from @Samples;

提示: Len() :“返回指定字符串表达式的字符数, 不包括尾随空格 ." DataLength DataLength( UnicodeStringExpression ) / DataLength( N'#' ) 获取字符长度。一般来说 DataLength( Left( Coalesce( StringExpression, '#' ), 1 ) ) 将返回每个字符的字节数。