嘗試在sql上使用替換函式但使用通配符-有解無憂

這是我第一次發帖，所以希望我能正確地獲得所有需要的資訊。

我試圖在有一組字符但通配符陳述句不起作用的行上執行更新替換陳述句，下面是一個選擇陳述句示例

SELECT replace(column, '[aA][bB][cC]-', '')
FROM table
where column like '%[aA][bB][cC]-%'

一個示例行是“abc-123 aBc-123 abC-abc Def-123”，但我需要它回傳“123 123 abc”

這是一個基本示例，但我試圖擺脫字串中任意位置的一組 3 個字符和一個“-”字符。abc 可以更改為 def，但“-”字符將始終出現在后面。

我已經做了一些谷歌搜索，但找不到合適的解決方案，因為大多數示例只會洗掉 abc 的一個示例-我需要洗掉所有版本。我正在運行 sql server (SQL Server 2014) 12.0 版，所以我認為有些功能我無法使用。

我認為我能找到的最接近的例子是在 T-SQL 中對字符范圍使用通配符，但我不能使用 translate 函式。

編輯：下面是一個創建的表的例子

 CREATE TABLE String_Removal_Example( 
someValue VARCHAR(100) 
) 
INSERT INTO String_Removal_Example (someValue) VALUES ('abc-123') 
INSERT INTO String_Removal_Example (someValue) VALUES ('abc-123 ABC-123') 
INSERT INTO String_Removal_Example (someValue) VALUES ('abc-123 ABc-123 123-ABC DEF-123')

選擇陳述句帶回

someValue
abc-123
abc-123 ABC-123
abc-123 ABc-123 123-ABC DEF-123

編輯 2：如果無法以這種方式執行此操作，則可能的替代方法是洗掉 3 個字符和 - 字符。我試過下面的

select Substring (someValue, Charindex( '-', someValue )   1, Len(someValue)) From String_Removal_Example

但這會回傳以下內容，這僅影響 nnn- 的第一個實體。

123
123 ABC-123
123 ABc-123 123-ABC DEF-123

Edit3：我需要替換的字串是 nnn- 為了澄清。我正在嘗試使用 [aA][bB][cC]- 格式，以防我需要更改它。它將始終是 3 個字符后跟一個“-”字符

uj5u.com熱心網友回復：

您可以創建這樣的函式：（我將其命名為ThreeLetters，但名稱中有什么...）

CREATE FUNCTION [dbo].[ThreeLetters] 
(
    @p0 VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @p1 VARCHAR(20) = '%[A-Z][A-Z][A-Z]-%';
    DECLARE @Result VARCHAR(MAX) = @p0;
    DECLARE @pos INT = PATINDEX(@p1,@Result COLLATE SQL_Latin1_General_CP1_CS_AS);
    DECLARE @pos2 INT ;
    DECLARE @i INT =0;

    WHILE @pos>0 and @i<10
    BEGIN
       SET @pos2 = CHARINDEX('-',@Result,@pos)-@pos;
       SELECT @Result = SUBSTRING(@Result,1,@pos-1) SUBSTRING(@Result, @pos @pos2 1, len(@Result));
       SET @pos = PATINDEX(@p1,@Result COLLATE SQL_Latin1_General_CP1_CS_AS);
       SET @i = @i   1;
    END 
    RETURN @Result
END

輸出您的樣本資料：

某個值	（無列名）
abc-123	123
abc-123 ABC-123	123 123
abc-123 ABc-123 123-ABC DEF-123	123 123 123-ABC 123

見DBFIDDLE

PS 檢查@i是在除錯時引入的。它可能不需要，但懶得洗掉它。

PPS 我正在洗掉 ThreeLetters 后跟 a -，這就是為什么123-ABC保持不變。

uj5u.com熱心網友回復：

這是一個 XML 技巧，它也適用于 Sql Server 2014

（在 Sql Server 2016 及更高版本中，string_split函式會更好。）

declare @String_Removal_Example table ( 
 someValue varchar(100) not null
);

insert into @String_Removal_Example (someValue) VALUES 
  ('abc-123') 
, ('abc-123 ABC-123') 
, ('abc-123 ABC-123 123-Abc DEF-123');

select ca.value as someValue
from @String_Removal_Example t
cross apply 
(
  select rtrim((
    select node.n.value('.','varchar(30)') ' '
    from 
    (
      select cast('<x><a>'   replace(replace(t.someValue, 
                  ' ', '</n><a>'), 
                  '-', '</a><n>')  
                  '</n></x>' as xml) as x
    ) q
    cross apply x.nodes ('/x/n') AS node(n)
    for xml path('') 
   )) as value
) ca
where lower(t.someValue) like '%[a-z0-9]-[a-z0-9]%';
GO

| 一些價值 |
| :-------------- |
| 123 |
| 123 123 |
| 123 123 美國廣播公司 123 |

db<>在這里擺弄

uj5u.com熱心網友回復：

請嘗試以下解決方案。它是@LukStorms 解決方案的變體。

它基于 XML 和 XQuery。他們的資料模型基于有序序列。

XML 允許我們標記輸入字串。

它正在尋找 'nnn-' 字串并將它們過濾掉。

例如，第 2 行被轉換為 XML，如下所示：

<root>
  <r>abc-</r>
  <r>123</r>
  <r>ABC-</r>
  <r>123</r>
</root>

查詢陳述句

-- DDL and sample data population, start
declare @String_Removal_Example table ( 
 someValue varchar(100) not null
);
insert into @String_Removal_Example (someValue) VALUES 
  ('abc-123') 
, ('abc-123 ABC-123') 
, ('abc-123 ABC-123 123-Abc DEF-123');
-- DDL and sample data population, end

SELECT someValue AS [before], [after]
FROM @String_Removal_Example
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
         REPLACE(REPLACE(someValue, '-', '- '), SPACE(1), ']]></r><r><![CDATA[')   
         ']]></r></root>' AS XML)
    .query('data(/root/r[not(contains(text()[1],"-"))])')
    .value('.','VARCHAR(255)')) AS t1(after);

輸出

 --------------------------------- ----------------- 
|             before              |      after      |
 --------------------------------- ----------------- 
| abc-123                         | 123             |
| abc-123 ABC-123                 | 123 123         |
| abc-123 ABC-123 123-Abc DEF-123 | 123 123 Abc 123 |
 --------------------------------- -----------------

uj5u.com熱心網友回復：

這基本上是在有空格的地方劃分字串，然后查找在第 4 個位置有“-”的“單詞”，洗掉前綴，然后將其重新拼接在一起：

SELECT s.someValue,
    STUFF((SELECT ' '   SUBSTRING(value, 5, LEN(value) - 4)
    FROM STRING_SPLIT(someValue, ' ') 
             WHERE CHARINDEX('-', value) = 4 
             FOR XML PATH('')),1,1,'') a
FROM String_Removal_Example s

uj5u.com熱心網友回復：

由于 SQL Server 中沒有正則運算式替換，我認為您可以可靠地使用模式替換的唯一方法是通過迭代程序。其中一種方法是使用遞回 CTE：

WITH Data AS
(   SELECT  SomeValue
    FROM (VALUES 
            ('abc-123'), 
            ('abc-123 ABC-123'), 
            ('abc-123 aBc-123 abC-abc Def-123'),
            ('abc-123 ABCD-123'), 
            ('abc-123 A$C-123')
        ) x (SomeValue)
), RegexReplace AS
(   SELECT  SomeValue, NewValue = CONVERT(VARCHAR(100), Data.SomeValue), Level = 1
    FROM    Data
    UNION ALL
    SELECT  SomeValue, CONVERT(VARCHAR(100), STUFF(NewValue, PATINDEX('% [A-z][A-z][A-z]-%', CONCAT(' ', NewValue)), 4, '')), Level   1
    FROM    RegexReplace AS d
    WHERE   PATINDEX('% [A-z][A-z][A-z]-%', CONCAT(' ', NewValue)) > 0
), RankedData AS
(   SELECT  *, RowNumber = ROW_NUMBER() OVER(PARTITION BY rr.SomeValue ORDER BY rr.Level DESC)
    FROM    RegexReplace AS rr
)
SELECT  rd.SomeValue, rd.NewValue
FROM    RankedData AS rd
WHERE   rd.RowNumber = 1;

這使：

某個值	新價值
abc-123	123
abc-123 ABC-123	123 123
abc-123 abc-123 abc-abc Def-123	123 123 美國廣播公司 123
abc-123 A$C-123	123 A$C-123
abc-123 ABCD-123	123 ABCD-123

這基本上用于PATINDEX()查找模式的第一個實體，然后STUFF()洗掉找到索引后的 4 個字符。然后重復此程序，直到PATINDEX()找不到匹配項為止。

可能還需要注意的是，在執行此操作時，PATINDEX()我在字串的開頭添加了一個空格，并在模式的開頭添加了一個空格。這確保它僅限于 3 個字符后跟一個連字符，如果超過 3 個（例如 ABCD-123），則整個字串保持完整，而不是A123如上面最后一行所示。在要搜索的字串的開頭添加空格可確保即使模式位于字串的開頭，仍然可以找到該模式。

uj5u.com熱心網友回復：

SELECT replaceAll(column, '[aA][bB][cC]-', '')
FROM table
where column like '%[aA][bB][cC]-%'

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/366944.html

標籤：sql sql-server sql-server-2014

上一篇：SQLServer：如何在一列中拆分字串并將值分配給另一列（沒有額外的行！）

下一篇：如何從SQL中的當前日期獲取過去六個月的前一個月資料