SQL Server replace, remove all after certain character
248,465
Solution 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)
Solution 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)
Solution 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
Solution 4
UPDATE MyTable
SET MyText = SUBSTRING(MyText, 1, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0
Solution 5
Use CHARINDEX
to find the ";". Then use SUBSTRING
to just return the part before the ";".
Comments
-
Jimmy almost 2 years
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 ";"
-
Mike over 14 yearsWhat if MyText does not contain the ';' character? In that case, wouldn't you be using a negative 1 as the second parameter in left(). In that case, on my box, I get an error of "Invalid length parameter passed to the substring function."
-
Etienne Dupuis over 13 years@Mike That is exactly my problem atm - See next Answer from @najmeddine!!
-
Tim over 10 years@Mike Rashlien's response below resolves this: LEFT(MyText+';', CHARINDEX(';',MyText+';')-1)
-
Paul Williams over 10 yearsNote that the WHERE clause above skips rows that do not have a semicolon. The OP wanted to remove the semicolon and any text after it. If there is no semicolon, there is nothing to remove, so the UPDATE statement will skip that row.
-
Paul Williams about 9 yearsUpdated this answer with some test cases to show how it works with missing semicolons, NULLs, empty strings, and additional semicolons.