SQL Server replace, remove all after certain chara

Question

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:

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 ";"

Answer 1

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)

Answer 2

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.

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.

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

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO  
   

2. 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.

   • create new class library project:

     

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

     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:

     

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

     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
     

Answer 3

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

Answer 4

Use LEFT combined with 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.

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

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)

Answer 5

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

Answer 6

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