Why doesn't SQL Srvr have function for proper case

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 2005 Forums

Transact-SQL (2005)

Why doesn't SQL Srvr have function for proper case

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2008-07-24 : 08:08:45

Is there a reason t-sql does not have a built in function for proper case of names e.g. bob o'conner = Bob O'Conner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 08:11:46

No, not really.

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 08:18:38

sounds like a presentation layer issue to me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 08:22:33

Don't steal Madhis byline!

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 08:24:41

i'm just filling in till he gets here

bogey
Posting Yak Master

166 Posts

Posted - 2008-07-24 : 08:27:40

quote:
Originally posted by elancaster

sounds like a presentation layer issue to me

Em

We are receiving data from outside sources who are capitalizing all letters. We would like to clean the data before putting into production.

The ultimate questions is "Is this standard in DBMS?" and if so "What's the reason?"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-24 : 08:58:23

i don't think any dmbs has this implemented.
IMO there is no reason other than it's not really important enough. which it isn't.
look here in the script library forum for a method that does what you need.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-24 : 09:42:42

I think the whole idea of a standardized "ProperCase" function is bogus, and that is why SQL does not have it. What is proper case formatting varies from language to language, from country to country, from business to business, and from application to application.
You should be able to write a function that suits your particular needs without much trouble.

e4 d5 xd5 Nf6

bogey
Posting Yak Master

166 Posts

Posted - 2008-07-24 : 09:50:43

Good answer -- My reason for asking the question today is I have developers in my shop who have exposure to other Database Systems, one being rBase which has this built in. I freq receive questions as to "Why doesnt sql server have this?" and so I post the question here.

Thanks for all your responses.

quote:
Originally posted by blindman

I think the whole idea of a standardized "ProperCase" function is bogus, and that is why SQL does not have it. What is proper case formatting varies from language to language, from country to country, from business to business, and from application to application.
You should be able to write a function that suits your particular needs without much trouble.

e4 d5 xd5 Nf6

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-24 : 09:53:25

Try this one

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[fn_SetFirstUcase]

(@String varchar(8000)

)

RETURNS @x1 TABLE

(

Val varchar(500)

)

AS

BEGIN

declare @xml as xml
set @xml= '<i>'+ replace(@String,' ','</i><i>') + '</i>'

declare @x as table(value varchar(50))
insert into @x
select left(x.i.value('.','varchar(50)'),1)
+ '' + lower(right(x.i.value('.','varchar(50)'),
len(x.i.value('.','varchar(50)'))-1))
from @xml.nodes('/i')x(i)
insert into @x1
select distinct(select cast(value + ' ' as varchar(50)) from @x x2 for xml path(''))
from @x x1

RETURN

END

select * from table cross apply fn_SetFirstUcase(table.columnname)b

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-24 : 10:44:37

I can guarantee you that the implementation in other databases, such as rbase, are not correct all of the time, and are not appropriate for many (if not most) business situations.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources