If you are using SQL Server 2005, you can cast the text column to
varchar(max) or nvarchar(max) and apply the UPPER function to that, and
then cast it back to text again. Here's an example:



create table #txt(t text)
truncate table #txt
declare @x varchar(max)
set @x = replicate(cast(' abcdefghi' as varchar(max)), 1500)
insert into #txt values(@x)
select t, len(cast(t as varchar(max))), upper(cast(t as varchar(max))),
len(upper(cast(t as varchar(max)))) from #txt
update #txt set t = cast(upper(cast(t as varchar(max))) as text)
select t, len(cast(t as varchar(max))) from #txt
drop table #txt



------------------------------------
Information Services Group, Inc.
Jim Gawn
Consultant
[Email address protected]
200 Airport Road, Suite 100
New Cumberland PA 17070
USA
tel: +1-717-774-0709 ext 23
fax: +1-717-774-3637
------------------------------------

-----Original Message-----
From: Aaron Jordan
[mailto:mssqldba-ezmlmshield-x60458829.[Email address protected]
Sent: Wednesday, December 27, 2006 11:16 am
To: LazyDBA Discussion
Subject: Uppercasing Text Datatype

Anybody have a way for uppercasing a field defined as a text datatype?

I tried making a UDF to parse 8000 characters at a time and use the
UPPER function on each substring, which worked, but with no way to
declare and return a text variable within a UDF, I didn't have a way to
reassemble the converted data and send it back.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page