Sostituire SQL Server, rimuovere tutto dopo un determinato carattere

I miei dati sembra

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

Come posso aggiornare MyText per eliminare tutto dopo il punto e virgola e il punto e virgola, quindi mi rimane il seguente:

 ID MyText 1 some text 2 text again 

Ho esaminato Sostituire SQL Server , ma non riesco a pensare a un metodo valido per il controllo di “;”

Usa SINISTRA in combinazione con CHARINDEX:

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

Si noti che la clausola WHERE salta le righe di aggiornamento in cui non vi è alcun punto e virgola.

Ecco un codice per verificare l’SQL sopra funziona:

 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 

Ottengo i seguenti risultati:

 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) 

Per i tempi in cui alcuni campi hanno un “;” e alcuni non puoi anche aggiungere un punto e virgola al campo e usare lo stesso metodo descritto.

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

Potrebbe usare CASE WHEN lasciare quelli senza ‘;’ solo.

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

Usa CHARINDEX per trovare “;”. Quindi utilizzare SUBSTRING per restituire la parte prima di “;”.

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

Per situazioni in cui ho bisogno di sostituire o abbinare (trovare) qualcosa contro stringa preferisco usare le espressioni regolari.

Poiché le espressioni regolari non sono completamente supportate in T-SQL è ansible implementarle utilizzando le funzioni CLR . Inoltre, non è necessaria alcuna conoscenza di C# o CLR poiché tutto ciò che serve è già disponibile nell’esempio delle funzioni dell’utilità di stringa MSDN.

Nel tuo caso, la soluzione che utilizza le espressioni regolari è:

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

Ma l’implementazione di tale funzione nel database ti aiuterà a risolvere problemi più complessi.


L’esempio seguente mostra come distribuire solo la funzione [dbo].[RegexReplace] , ma ti consiglierò di distribuire l’intera class di String Utility .

  1. Abilitazione dell’integrazione CLR. Esegui i seguenti comandi Transact-SQL:

     sp_configure 'clr enabled', 1 GO RECONFIGURE GO 
  2. Inserimento del codice (o creazione del file .dll ). Generraly, puoi farlo usando il prompt dei comandi di Visual Studio o .NET Framework (come mostrato nell’articolo), ma preferisco usare Visual Studio.

    • creare un nuovo progetto di libreria di classi:

      inserisci la descrizione dell'immagine qui

    • copia e incolla il seguente codice nel 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); } } 
    • crea la soluzione e ottieni il percorso del file .dll creato:

      inserisci la descrizione dell'immagine qui

    • sostituire il percorso del file .dll nelle seguenti istruzioni T-SQL ed eseguirli:

       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 
    • Questo è tutto. Metti alla prova la tua funzione:

       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