December 16, 2014 at 1:07 pm
Hi, I am an amateur with SQL and much better with ASP.
I have 10,000 records in a SQL Server DB and about 200 of them have some rubbish data right at the END of one field which I want to remove.
I have figured out I can do it like this:
update MYTABLE set ProductDescription = Left(ProductDescription,charindex('(function(d',ProductDescription)-2)
My rubbish data always starts with this string: (function(d
However this only works when the string is found, for entries where the string is not there it falls over as the value of the CHARINDEX in that case must be returned as 0 so then the LEFT function does not work.
Could someone kindly point me to a simple solution to do this?
I think I must need something like an "IF EXISTS" type statement?
Many thanks in advance for any help - I have been scratching my head all day trying to figure out a clean, easy solution.
December 16, 2014 at 1:22 pm
update MYTABLE set ProductDescription = Left(ProductDescription,charindex('(function(d',ProductDescription)-2)
WHERE ProductDescription LIKE '(function(d%'
December 16, 2014 at 1:59 pm
Thanks - I should have known that to be honest and I am kicking myself.
BTW you missed out the first % sign:
update MYTABLE set ProductDescription = Left(ProductDescription,charindex('(function(d',ProductDescription)-2)
WHERE ProductDescription LIKE '%(function(d%'
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply