Changing from TitleCase to UPPER case

Transact-SQL
1

Hi everyone

I have a database (circa 300,000 records) that have a variety of countries listed as e.g. UNITED KINGDOM and United Kingdom.

I want all the countries to be shown in upper case, but I have hit a couple of problems.

If I use e.g.
UPDATE contact
SET country=upper(country)
WHERE serialnumber='100045'

... then I get a message saying that the query updated 4 rows, even though that serial number (and therefore country) only appear once in the contact table.

The second issue I have is if I try to use e.g.
UPDATE contact
SET country='UNITED KINGDOM' where country='United Kingdom'

With this script I get a SQL Server timeout error because there are around 200,000 entries. Also, I don't think SQL Server is seeing the TitleCased countries as TitleCase - I think it's just trying to update all entries to UPPER case. This is kind of find, but will also take longer due to the sheer amount of entries in the table.

Does anyone have any solutions to my problems please?

Many thanks
Jon

2

UPDATE TOP (20000) contact
SET country='UNITED KINGDOM'
WHERE country COLLATE Latin1_General_Bin <> 'UNITED KINGDOM' COLLATE Latin1_General_Bin

1 Like
3

Check for a trigger on the contact table - if you can select from the table where serialnumber = '100045' and get back a single row, but see 4 rows updated then there is probably a trigger updating another table (audit table?).

1 Like
4

Hi

another thing you can try is

select the columns being returned by the update

example

update statement

UPDATE contact
SET country=upper(country)
WHERE serialnumber='100045'

corresponding select

select country from contact WHERE serialnumber='100045'

see the data . how many rows returning .. as expected etc etc etc

looks like jeff already covered this above

1 Like
5

Thanks Scott, that worked like a charm :slight_smile:

6

Thanks Jeff - yes, there were 3 other triggers on the table.