Converting a text string to all caps 2

[Sashanan]

I'm looking for an SQL solution to put the text of one columns in all caps, i.e. "Text" becomes "TEXT". I had expected results with the UPPER command as follows:

UPDATE MyTable
SET MyCol = UPPER(MyCol)

Problem is, although the query seems to execute correctly, it didn't actually change anything. "Text" remained "Text".

What did I do wrong here? Should I use a different command entirely? In case it makes a difference, this is in SQL Server 7 (so a T-SQL solution suits me just as well).

Thanks in advance!


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[swampBoogie]

No, the upper function is the one to be used.

What does

select upper(mycol) from mytable

return?

 

[Sashanan]

Using the SELECT command gives the expected results, showing each of the records with that column in all caps. Yet I just tried it in the UPDATE query again and although it says "X records affected by query" (and the X is correct), they have simply not changed at all.

However, I got an interesting result from the following query:

UPDATE TestTabelUpperCase
SET TestKolom = UPPER(TestKolom)
WHERE TestKolom <> UPPER(TestKolom)

This tells me &quot;Query has executed successfully&quot; without having affected any records. Suggests that SQL Server does not recognize the difference between the string being in caps or not, which could explain why this query's not working out. Though I have no idea what to do about that...


&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)

 

[JamesLean]

I wouldn't expect your second query to update any rows, unless you were using a case-sensitive collation (which you're probably not).

Are you sure that your first update is ALL you are executing? Try this test and see if it works:

create table #t (c varchar(10))

insert #t values ('lowercase')
insert #t values ('MiXedCasE')
insert #t values ('UPPERCASE')

select * from #t

update #t
set c = UPPER(c)

select * from #t

drop table #t

You should get all the values changed to uppercase in the second select.

--James

 

[Sashanan]

The test worked fine, then when I tried to run the exact same thing on my own test table, it still wouldn't work. That's where I got the thought the primary key might have been messing it up, so I created a second column in my test table and made that the primary key instead.

The instant I did that all the text values I'd been trying to update earlier all jumped into caps right away. So seems the primary key was just messing up my test here. *runs his UPPER query on the tables he's actually trying to update here*

...and that worked perfectly too. Test table was messing with me all along.

Thanks for the help, folks!


&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)