SQL Server替换，删除所有后的字符。

My data looks like

我的数据看起来像

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

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

如何更新MyText，以便在分号和分号之后删除所有内容，因此，我只剩下以下内容:

ID    MyText
1     some text
2     text again

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

我查看了SQL Server Replace，但想不出一种检查“;”的可行方法;

6 个解决方案

#1

Use LEFT combined with CHARINDEX:

使用左结合CHARINDEX:

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

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

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

Here is some code to verify the SQL above works:

下面是一些验证上述SQL工作的代码:

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

I get the following results:

我得到以下结果:

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)

#2

For the times when some fields have a ";" and some do not you can also add a semi-colon to the field and use the same method described.

对于某些字段有“;”的时候，也可以在字段中添加分号，并使用相同的方法。

SET MyText = LEFT(MyText+';', CHARINDEX(';',MyText+';')-1)

#3

Could use CASE WHEN to leave those with no ';' alone.

可以用例当把那些没有';'单独。

    SELECT
    CASE WHEN CHARINDEX(';', MyText) > 0 THEN
    LEFT(MyText, CHARINDEX(';', MyText)-1) ELSE
    MyText END
    FROM MyTable

#4

Use CHARINDEX to find the ";". Then use SUBSTRING to just return the part before the ";".

使用CHARINDEX查找“;”。然后使用子字符串返回“;”之前的部分。

#5

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

#6

For situations when I need to replace or match(find) something against string I prefer using regular expressions.

对于需要替换或匹配(查找)字符串的情况，我更喜欢使用正则表达式。

Since, the regular expressions are not fully supported in T-SQL you can implement them using CLR functions. Furthermore, you do not need any C# or CLR knowledge at all as all you need is already available in the MSDN String Utility Functions Sample.

由于T-SQL中不完全支持正则表达式，所以可以使用CLR函数实现它们。此外，您根本不需要任何c#或CLR知识，因为您所需要的一切都已经在MSDN字符串实用函数示例中可用了。

In your case, the solution using regular expressions is:

在你的例子中，使用正则表达式的解决方案是:

SELECT [dbo].[RegexReplace] ([MyColumn], '(;.*)', '')
FROM [dbo].[MyTable]

But implementing such function in your database is going to help you solving more complex issues at all.

但是在数据库中实现这样的功能将有助于您解决更复杂的问题。

The example below shows how to deploy only the [dbo].[RegexReplace] function, but I will recommend to you to deploy the whole String Utility class.

下面的示例展示了如何只部署[dbo]。[RegexReplace]函数，但我将建议您部署整个字符串实用程序类。

Enabling CLR Integration. Execute the following Transact-SQL commands:

使CLR集成。执行以下Transact-SQL命令:
```
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO  
```

Bulding the code (or creating the .dll). Generraly, you can do this using the Visual Studio or .NET Framework command prompt (as it is shown in the article), but I prefer to use visual studio.

填充代码(或创建.dll)。一般来说，您可以使用Visual Studio或. net Framework命令提示符(如本文所示)来实现这一点，但我更喜欢使用Visual Studio。

create new class library project:

创建新的类库项目:

copy and paste the following code in the Class1.cs file:

复制并粘贴Class1中的以下代码。cs文件:

using System;
using System.IO;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public sealed class RegularExpression
{
    public static string Replace(SqlString sqlInput, SqlString sqlPattern, SqlString sqlReplacement)
    {
        string input = (sqlInput.IsNull) ? string.Empty : sqlInput.Value;
        string pattern = (sqlPattern.IsNull) ? string.Empty : sqlPattern.Value;
        string replacement = (sqlReplacement.IsNull) ? string.Empty : sqlReplacement.Value;
        return Regex.Replace(input, pattern, replacement);
    }
}

build the solution and get the path to the created .dll file:

构建解决方案并获取创建的.dll文件的路径:

replace the path to the .dll file in the following T-SQL statements and execute them:

在下面的T-SQL语句中替换.dll文件的路径并执行它们:

IF OBJECT_ID(N'RegexReplace', N'FS') is not null
DROP Function RegexReplace;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'StringUtils')
DROP ASSEMBLY StringUtils;
GO

DECLARE @SamplePath nvarchar(1024)
-- You will need to modify the value of the this variable if you have installed the sample someplace other than the default location.
Set @SamplePath = 'C:\Users\gotqn\Desktop\StringUtils\StringUtils\StringUtils\bin\Debug\'
CREATE ASSEMBLY [StringUtils] 
FROM @SamplePath + 'StringUtils.dll'
WITH permission_set = Safe;
GO


CREATE FUNCTION [RegexReplace] (@input nvarchar(max), @pattern nvarchar(max), @replacement nvarchar(max))
RETURNS nvarchar(max)
AS EXTERNAL NAME [StringUtils].[RegularExpression].[Replace]
GO

That's it. Test your function:

就是这样。测试功能:

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    

SELECT [dbo].[RegexReplace] ([MyText], '(;.*)', '')
FROM @MyTable

select * from @MyTable

#1