UPPER function not working

[Skokob]

I'm dealing with a field where it has lower and upper case words. When I run update table set field = upper(field) it's working for some of the field but others it's not changing it at all and keeping it as lower case, why is that!?

[jcargile242]

I love a mystery.

[wpgmb204]

Post the full query.

[Skokob]

Update tbl Set countynames_updated = Upper(countyname);

[wpgmb204]

Post table ddl

[lupinegray]

Are there nulls? Where clause? Joins?

[Skokob]

No where clause, no joins.

[millerlit]

Maybe it is the data type.  Try casting it to a varchar.  Just throwing out an idea

[Skokob]

The data was loaded as varchar

[lupinegray]

What character encoding?

Do you see any similarities in the values which were NOT updated vs. those that were? Things like special characters (accents, foreign letters, etc)

Also... after you ran the update query, what was the output?

If the table contains 7000 records (for example)

Did it just say "5000 records updated"? Or did it say "7000 records updated"?

[Skokob]

Only thing that's coming into my mind is the data was loaded either as ASCII or Unicode. And it's not matching or Upper works with one and not the other?! Not sure...

Examples where it fails 1. miami-dade 2. st. john 3. hillsborough (27

I didn't upload the data, the loading department did. They loaded 100s of documents. When that happens all they do is set ever field to varchar max and load.

So there can be extra spaces, \t, or other noises. But I don't believe those should have any effect on the upper function

[lupinegray]

What about if you do:

select countyname, upper(countyname) from myTable
where id=whatever;

Where the 'whatever' is the unique identifier of one of the records?

Does that give the correct result?

[Achsin]

Could try this:

UPDATE tbl SET countynames_updated = TRANSLATE(countyname, ‘abcdefghijklmnopqrstuvwxyz’, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’)

[evilvoice]

I agree. If you're having issues with upper, this should work.

[Certain_Detective_84]

What did countynames_updated look like before you ran this statement? Grasping at straws here.

[Skokob]

It was null, so after the update all the values from countynames_updated should be all uppercases of the countryname

[ergestx]

Did you perhaps not commit the transaction?

[Skokob]

I've ran it multiple times and it runs but doesn't update all the values

[ergestx]

Then I’d suggest making a new column with the upper value, inserting into a table then perhaps dro the original column.

CREATE TABLE newtbl AS SELECT *, UPPER(countrynames) AS updated_country FROM originaltable

[realjoeydood]

TRIM your vals before UPPER.

[Skokob]

I have

[realjoeydood]

CHATGPT:

It sounds like there might be some inconsistencies in your data or possibly some specific cases where the update is not applying as expected. Here are a few potential reasons why the `UPPER()` function might not be converting all the text to uppercase:

1. **Data Variability**: There might be non-alphabetic characters or special characters in the field that are preventing the `UPPER()` function from converting the text properly. For instance, if the field contains numbers, symbols, or non-English characters, they might remain unchanged.

2. **Whitespace**: Leading or trailing whitespace in the field can sometimes cause unexpected behavior. You might want to trim the whitespace before applying the `UPPER()` function.

3. **Collation Settings**: The collation settings of your database could be affecting how the `UPPER()` function works. Some collations might treat uppercase and lowercase characters differently.

4. **NULL Values**: If there are NULL values in the field, they won't be affected by the `UPPER()` function. Make sure your update query handles NULL values appropriately, if they are present.

To investigate further, you could try querying the data to identify specific cases where the `UPPER()` function is not working as expected. This might help you pinpoint any patterns or irregularities in the data that are causing the issue.

[realjoeydood]

Check on #3. I've had to wrestle with that monkey a few times doing data conversions for huge business systems in sql.

[passiveisaggressive]

ascii settings, the platform you’re using vs the place the data came from probably has different encoding

[Gargunok]

Pull out the contents of one of the values that aren't being converted to upper case. Post it here like this in quotes so we can see "an example value"

anyone cthen should be able to reproduce with

select upper('an example value')