Geek Answers Handbook

How to find a substring and delete it, as well as everything that happens after it

I was looking for a way to update some columns of a specific table.

This update will try to find a specific substring and delete it, as well as all other characters that appear after it.

It is easy to delete all data after a specific character, but I cannot find a way to do the same with a substring.

thanks for the help

+4
source share
2 answers

Given that:

  • updated column called haystack
  • substring to search called @needle

Here is the expression you are looking for:

 case when charindex(@needle, haystack) = 0 then @haystack else substring(haystack, 1, charindex(@needle, haystack) - 1) end

Here's an online test of two cases (match / no match): http://www.sqlfiddle.com/#!3/d41d8/21209

+1
source 
 DECLARE @substring varchar(32) = 'anySubString' UPDATE myTable SET colomnName = (CASE WHEN charindex(@substring, colomnName) = 0 THEN colomnName ELSE substring(colomnName , 1, charindex(@substring, colomnName) - 1) END) END
0
source

More articles:


All Articles