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

Answer 1

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 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)