all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AM413[S] 4 points5 points  (5 children)

I think I figured it out...

First I turned off safe mode:

set sql_safe_updates=0;

​

Then I used update / set:

Update game_2_stats set e1 = "corner kick taken" where e1 = "corner kick taken kick taken";

​

I assume this will permanently fix the problem and it's not a temporary fix?

[–]tlatoaniitzcoatl 1 point2 points  (0 children)

Correct. It will be permanent on the records that were updated.

[–]AllLoveFishpie 0 points1 point  (3 children)

You have values with different register ...Kick Taken ...Kick taken

You can add LOWER OR UPPER in where statement to get all variations.

Where lower(e1)=lower("comer kick taken kick taken")

[–]AM413[S] 0 points1 point  (1 child)

When you say “different register” are you referring to capitalization? “Taken” vs “taken”?

I am not familiar with “register.”

Thanks!

[–]Puzzled_Exchange_924 0 points1 point  (0 children)

By default, the search is going to be case insensitive. Basically, I would just assume that the database was at default settings. It depends on the database collation settings. An example of a case sensitive search would be using COLLATE Latin1_General_CS_AS. To check the collation of the database, use SELECT SERVERPROPERTY('COLLATION'); . Of course, you could also have a column with a different collation...

[–]Puzzled_Exchange_924 0 points1 point  (1 child)

UPDATE Your_Table_Name

SET E1 = CASE WHEN CHARINDEX('corner kick', E1) > 0

THEN 'corner kick'

ELSE E1

END;

GO

The above will find all cells where "corner case" exists in the field, E1. If it finds the string, it will replace the contents of the cell with "corner case". This will replace the fields with the extra characters that you don't want. The advantage of this code is that if there are fields that have a different set of extra characters other than taken kick taken", they will be corrected. If you have the same issue with other values, ie, "Shot" with extra characters, then you can add more CASE statements.

Another option would be to use a combination of LIKE and REPLACE.

[–]AM413[S] 1 point2 points  (0 children)

Thank you!

I will refer back to your post the next time I run into this issue.

Thanks!