SQL Server replace, remove all after certain character

by

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)

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. How do I UPDATE from a SELECT in SQL Server?
  3. Get top 1 row of each group
  4. How to return only the Date from a SQL Server DateTime datatype
  5. How to calculate age (in years) based on Date of Birth and getDate()
  6. CROSS JOIN vs INNER JOIN in SQL
  7. Referring to a Column Alias in a WHERE Clause
  8. Convert Month Number to Month Name Function in SQL
  9. Function vs. Stored Procedure in SQL Server
  10. Multiple INSERT statements vs. single INSERT with multiple VALUES
  11. Limit on the WHERE col IN (…) condition
  12. Removing leading zeroes from a field in a SQL statement
  13. converting Epoch timestamp to sql server(human readable format)
  14. Getting result of dynamic SQL into a variable for sql-server
  15. How to get difference between two rows for a column field?
  16. How do I split a delimited string so I can access individual items?
  17. What’s the best way to select the minimum value from several columns?
  18. Counting DISTINCT over multiple columns
  19. SQL Server SELECT into existing table
  20. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  21. Why use Select Top 100 Percent?
  22. How to check if a stored procedure exists before creating it
  23. Correct use of transactions in SQL Server
  24. How do I prevent a database trigger from recursing?
  25. How to create a pivot query in sql server without aggregate function
  26. TSQL – How to use GO inside of a BEGIN .. END block?
  27. SQL Server FOR XML Path make repeating nodes
  28. Cumulative Total in MS Sql server [duplicate]
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment