SQL Server 替换，删除特定字符后的所有字符 [英] SQL Server replace, remove all after certain character

问题描述

我的数据看起来像

ID    MyText
1     some text; some more text
2     text again; even more text

如何更新 MyText 以删除分号之后的所有内容并包括分号，所以我只剩下以下内容:

How can I update MyText to drop everything after the semi-colon and including the semi colon, so I'm left with the following:

ID    MyText
1     some text
2     text again

我查看了 SQL Server Replace，但不能'没有想到检查;"的可行方法

I've looked at SQL Server Replace, but can't think of a viable way of checking for the ";"

推荐答案

使用 LEFT 与 CHARINDEX 结合:

Use LEFT combined with CHARINDEX:

UPDATE MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

请注意，WHERE 子句会跳过更新没有分号的行.

Note that the WHERE clause skips updating rows in which there is no semicolon.

这里有一些代码来验证上面的 SQL 是否有效:

Here is some code to verify the SQL above works:

declare @MyTable table ([id] int primary key clustered, MyText varchar(100))
insert into @MyTable ([id], MyText)
select 1, 'some text; some more text'
union all select 2, 'text again; even more text'
union all select 3, 'text without a semicolon'
union all select 4, null -- test NULLs
union all select 5, '' -- test empty string
union all select 6, 'test 3 semicolons; second part; third part;'
union all select 7, ';' -- test semicolon by itself    

UPDATE @MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

select * from @MyTable

我得到以下结果:

id MyText
-- -------------------------
1  some text
2  text again
3  text without a semicolon
4  NULL
5        (empty string)
6  test 3 semicolons
7        (empty string)

这篇关于SQL Server 替换，删除特定字符后的所有字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！