selecting all upper - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

selecting all upper

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-01-03 : 16:36:09

I want to select * from a table but want to return everything in upper case without specifying and certain column. Is this possible?

Select upper(*) from tablename

gives a syntax error...

Thanks,
Eddie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-03 : 16:44:27

Nah, that won't work because UPPER() only works on character columns; numeric, date, text etc. would cause it to fail. You'd have to list each column with the UPPER function individually.

You might be able to write a stored procedure that accepts a table name, queries the INFORMATION_SCHEMA view, and then generates some dynamic SQL to do something equivalent to SELECT UPPER(*), but ultimately it will do the same as above.

If you want to try it, search SQL Team for "dynamic SQL" and see if that helps.

Rafiq
Starting Member

25 Posts

Posted - 2002-01-04 : 01:01:55

Hi,

If you intrested, Please try to following method.It will work fine.

Create Procedure UpperOP
(
@p_TableName Varchar(128)
)
As
Begin
Declare @Obj_Id Int, @Qry_String Varchar(1000),
@m_ColName Varchar(256), @m_Xtype TinyInt

Set @Qry_String = 'Select '
Set @Obj_Id = Object_Id(@p_TableName)

Declare CurColName Cursor For Select Name, Xtype As ColumnDataType From SysColumns Where Id = @Obj_Id

Open CurColName
Fetch Next From CurColName Into @m_ColName, @m_Xtype

While @@Fetch_status = 0
Begin
/* 167 = Varchar, 175 = Char */
If @m_Xtype = 167 OR @m_Xtype = 175
Set @Qry_String = @Qry_String + 'Upper(' + @m_ColName + ') As ' + @m_ColName + ','
Else
Set @Qry_String = @Qry_String + @m_ColName + ','

Fetch Next From CurColName Into @m_ColName, @m_Xtype
End
Close CurColName
DeAllocate CurColName

Set @Qry_String = SubString(@Qry_String, 1, Len(@Qry_String)-1)
Set @Qry_String = @Qry_String + ' From ' + @p_TableName

Exec (@Qry_String)
End

Regards,
Rafiq

Rafi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources