SQL中专门替换字符串末尾的substring [英] Replace substring specifically at the end of the character string in SQL

问题描述

在 T-SQL 中，如何仅替换出现在字符串末尾的子字符串 (varchar)?

In T-SQL, how do I replace the occurrence of a substring only at the end of a character string (varchar)?

例如:

如果我用子串violence对字符串'violence begets Violence'进行运算，结果将是'violence begets'其他一些例子是

If I were to do the operation with substring violence on character string 'violence begets violence', the result would be 'violence begets ' Some other examples are

1)'the fox jumped over the fence' 带有子字符串 fox 将导致没有变化，因为 fox 不在最后字符串.

1)'the fox jumped over the fence' with substring fox would result in no change as fox is not at the end of the character string.

2)Can I kick the can 带有子字符串 can 将导致 Can I kick the

2)Can I kick the can with substring can would result in Can I kick the

3)gs_fs_pringles 带有子字符串 _fs 不会导致任何变化

3)gs_fs_pringles with substring _fs would result in no changes

4)gs_pringles_fs 带有子字符串 _fs 将导致 gs_pringles

4)gs_pringles_fs with substring _fs would result in gs_pringles

推荐答案

DECLARE @t TABLE(SomeString varchar(100))

INSERT @t VALUES ('violence begets violence'), ('the fox jumped over the fence'), ('Can I kick the can');

WITH CTE1 AS (
    SELECT
        CHARINDEX(' ', SomeString) AS LeftIndex,
        REVERSE(CHARINDEX(' ', SomeString)) AS RightIndex,
        RTRIM(SomeString) AS SomeString
    FROM
        @t
), CTE2 AS (
    SELECT
       LEFT(SomeString, LeftIndex-1) AS LeftWord,
       RIGHT(SomeString, RightIndex-1) AS RightWord,
       LeftIndex, RightIndex, SomeString
    FROM  
       CTE1
)
SELECT 
    CASE
        WHEN LeftWord <> RightWord THEN SomeString
        ELSE LEFT(SomeString, LEN(SomeString)-RightIndex)
    END
FROM
    CTE2;

这篇关于SQL中专门替换字符串末尾的substring的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！