SQL Server replace remove all after certain character

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

User · Accepted Answer

Use LEFT combined with CHARINDEX   UPDATE MyTable SET MyText   LEFT MyText  CHARINDEX      MyText  - 1  WHERE CHARINDEX      MyText   gt  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   gt  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

User · Answer

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    Enabling CLR Integration  Execute the following Transact-SQL commands   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    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

User · Answer

Could use CASE WHEN to leave those with no     alone        SELECT     CASE WHEN CHARINDEX      MyText   gt  0 THEN     LEFT MyText  CHARINDEX      MyText -1  ELSE     MyText END     FROM MyTable

User · Answer

UPDATE MyTable    SET MyText   SUBSTRING MyText  1  CHARINDEX      MyText  - 1   WHERE CHARINDEX      MyText   gt  0

User · Answer

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

User · Answer

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

[sql] SQL Server replace, remove all after certain character

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to replace