Textual Orientation - 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

Transact-SQL (2000)

Textual Orientation

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-18 : 18:11:10

It appears that there are only two functions that can be used to force cases in text: LOWER() & UPPER(), is there any other way to convert text to a normal orientation? For example: IF my input is all CAPS how can I set it so that it reads like normal text?

Sample code:

--Editing Automobiles

DROP TABLE #Automobiles
CREATE TABLE #Automobiles
(_Vehicle nvarchar (25) NULL)
GO

INSERT #Automobiles VALUES ('1955 DESOTO FIREFLITE ')
INSERT #Automobiles VALUES ('1959 OLDSMOBILE DELTA 88')
INSERT #Automobiles VALUES ('1967 CHEVROLET IMPALA ')
INSERT #Automobiles VALUES ('1968 CHRYSLER IMPERIAL ')
GO

SELECT * FROM #Automobiles
GO

...results
_Vehicle
-------------------------
1955 DESOTO FIREFLITE
1959 OLDSMOBILE DELTA 88
1967 CHEVROLET IMPALA
1968 CHRYSLER IMPERIAL

DESIRED RESULTS:
_Vehicle
-------------------------
1955 DeSoto Fireflite
1959 Oldsmobile Delta 88
1967 Chevrolet Impala
1968 Chrysler Imperial

Thanks for your help!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-18 : 18:25:03

You'll want to do this in the presentation layer, which is your application, rather than in the database. T-SQL can certainly do this, but it is not recommended to do so.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-18 : 19:23:23

I think Igor has a function that is supposed to do what you want on a link in this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50370

"PROPER() Returns from a character expression a string capitalized as appropriate for proper names"

I've never used it, but he's a very clever programmer, so it might be what you want.

Edit: Here's the code for that function


 -- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PROPER( ) User-Defined Function
 -- Returns from a character expression a string capitalized as appropriate for proper names.
 -- PROPER(@cExpression)
 -- Return Values nvarchar(4000)
 -- Parameters
 -- @cExpression nvarchar(4000) Specifies the character expression from which PROPER( ) returns a capitalized character string. 
 -- Example
 -- declare @gcExpr1 nvarchar(4000), @gcExpr2 nvarchar(4000)
 -- select @gcExpr1 = 'Visual Basic.NET', @gcExpr2 = 'VISUAL BASIC.NET'
 -- select dbo.PROPER(@gcExpr1)  -- Displays 'Visual Basic.net'
 -- select dbo.PROPER(@gcExpr2)  -- Displays 'Visual Basic.net'
 -- Remarks
 -- PROPER similar to the Oracle function PL/SQL  INITCAP 
 -- UDF the name and functionality of which correspond  to the  Visual FoxPro function  
CREATE function PROPER  (@expression nvarchar(4000))
returns nvarchar(4000)
as
    begin
      declare @i  smallint,   @properexpression nvarchar(4000),  @lenexpression  smallint, @flag bit, @symbol nchar(1)
      select  @flag = 1, @i = 1, @properexpression = '', @lenexpression =  datalength(@expression)/(case SQL_VARIANT_PROPERTY(@expression,'BaseType') when 'nvarchar' then 2  else 1 end) 

      while  @i <= @lenexpression
          begin
             select @symbol = lower(substring(@expression, @i, 1) )
               if @symbol in (nchar(32), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13),  nchar(160)) and ascii(@symbol) <> 0
                   select @flag = 1
               else
                   if @flag = 1
                       select @symbol = upper(@symbol),  @flag = 0 
              select  @properexpression = @properexpression + @symbol,  @i = @i + 1 
          end

     return @properexpression 
    end
GO

Edit #2:
I tested the PROPER() function with this code:


declare @str nvarchar(4000)
select @str = 'we must follow "the rules",or forever be lost'
select [Proper Output] = dbo.proper(@str)

Proper Output
-------------------------------------------------------------
We Must Follow "the Rules",or Forever Be Lost

(1 row(s) affected)

Notice the 2 errors after the quote and the comma.
I guess even Igor isn't perfect.

This is the way MS Word does the same string with Title Case:
We Must Follow "The Rules",Or Forever Be Lost

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-19 : 00:49:51

What is your Front End Application?
If you use VB6, use this

StrConv(yourTEXT, vbProperCase)

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-20 : 01:09:19

quote:
Originally posted by tkizer

You'll want to do this in the presentation layer, which is your application, rather than in the database. T-SQL can certainly do this, but it is not recommended to do so.

Tara Kizer
aka tduggan

Have to sort of kinda somewhat disagree. The data should be STORED in proper case format, and the db may required to do the conversion on import depending on the nature of the datasource. Any further formatting, such as converting it back to all upper-case, should be handled by the presentation layer.
Problem is, what IS proper case? In your situation you want to capitalize the first letters of each word (I guess). But of course, normal sentences do not operate that way, and you may have some exceptions such as model codes or common words (of, the, etc...) which are often left uncapitalized. So you are not going to find a general solution to your problem, though if lucky you may find one specific to it, or you will need to write one to handle any peculiar rules in your case (pun intended).

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-21 : 10:16:56

quote:

Originally posted by Michael Valentine Jones

I think Igor has a function that is supposed to do what you want on a link in this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50370

"PROPER() Returns from a character expression a string capitalized as appropriate for proper names"

I've never used it, but he's a very clever programmer, so it might be what you want.

Edit: Here's the code for that function


 -- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PROPER( ) User-Defined Function
 -- Returns from a character expression a string capitalized as appropriate for proper names.
 -- PROPER(@cExpression)
 -- Return Values nvarchar(4000)
 -- Parameters
 -- @cExpression nvarchar(4000) Specifies the character expression from which PROPER( ) returns a capitalized character string. 
 -- Example
 -- declare @gcExpr1 nvarchar(4000), @gcExpr2 nvarchar(4000)
 -- select @gcExpr1 = 'Visual Basic.NET', @gcExpr2 = 'VISUAL BASIC.NET'
 -- select dbo.PROPER(@gcExpr1)  -- Displays 'Visual Basic.net'
 -- select dbo.PROPER(@gcExpr2)  -- Displays 'Visual Basic.net'
 -- Remarks
 -- PROPER similar to the Oracle function PL/SQL  INITCAP 
 -- UDF the name and functionality of which correspond  to the  Visual FoxPro function  
CREATE function PROPER  (@expression nvarchar(4000))
returns nvarchar(4000)
as
    begin
      declare @i  smallint,   @properexpression nvarchar(4000),  @lenexpression  smallint, @flag bit, @symbol nchar(1)
      select  @flag = 1, @i = 1, @properexpression = '', @lenexpression =  datalength(@expression)/(case SQL_VARIANT_PROPERTY(@expression,'BaseType') when 'nvarchar' then 2  else 1 end) 

      while  @i <= @lenexpression
          begin
             select @symbol = lower(substring(@expression, @i, 1) )
               if @symbol in (nchar(32), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13),  nchar(160)) and ascii(@symbol) <> 0
                   select @flag = 1
               else
                   if @flag = 1
                       select @symbol = upper(@symbol),  @flag = 0 
              select  @properexpression = @properexpression + @symbol,  @i = @i + 1 
          end

     return @properexpression 
    end
GO

Edit #2:
I tested the PROPER() function with this code:


declare @str nvarchar(4000)
select @str = 'we must follow "the rules",or forever be lost'
select [Proper Output] = dbo.proper(@str)

Proper Output
-------------------------------------------------------------
We Must Follow "the Rules",or Forever Be Lost

(1 row(s) affected)