Converting To Mixed Case - 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)

Converting To Mixed Case

Author

Topic

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-02-13 : 13:57:26

Hi all,

Hope someone can help here. We have a database that stores name and address information from the various systems in use throughout the organisation. The problem is that we want to store the manes and addresses in Pascal Case IE MR WILLIAM ANCHOR would be stored as Mr William Anchor, but due to data entry validation (or a lack of it!!)/ System Limitations (Mainframe!) they are in Upper Case / Lower Case or Mixed Case. What I need is a SQL Statement that will allow me to Update the rows after a dataload.

Here is a fragment of the Person Table


DECLARE TABLE dbo.Person (
    PersonID Integer Constraint PK_Person_PersonID PRIMARY KEY
  , Surname Varchar (40)
  , FirstNames Varchar (120) )
GO
Insert Into dbo.Person Values ( 1 , 'WINDSOR' , 'CHarleS pHILIP ARTHUR GEORGE')
GO

Now I can write a function that converts it on an individual basis, and include that in the Update statement, but its not all that efficient, I am sure that there must be a better way. One of our developers has suggested doing it with a cursor, but he was dragged away to be taken out and shot

Ideas Much Appreciated

--
Regards
Tony The DBA

Kristen
Test

22859 Posts

Posted - 2006-02-13 : 14:11:00

Presumably you have all the variants to worry about too?

'Windsor Jnr', 'Charles'
'Windsor III', 'Dr Charles'
'Montbatten-Windsor', 'Charles'
'du Windsor', 'Charles'
'O'Windsor', 'Charles'
'McWindsor', 'Charles'
'Wallcarpet', 'Walter' [Sorry, couldn't resist!]

There have been a few threads here on the subject, the one I could easily find was:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=36195

and this, but I don't think it leads anywhere relevant:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=28159

Kristen

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-02-13 : 15:06:55

Thanks Kristen,

I don't think I was putting the right thing into the search engine [:)}

Yes there are all the variants . . . And then the De Duping as well

but thats another exercise, and I'm not yet being asked to get involved

The Function method may turn out to be the best after all, It'll be slow during the intial dataloads, but once we move on to incrementals it should be fairly quick. Just ran it for one colume in one table (107K Rows) and it completed in 18 Seconds which is acceptable (to me).

I am sure there is a way to do this in TSQL, I remember a thread from a while back, that involved a lot of IF Thens

--
Regards
Tony The DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources