Upper and Lower Case 1

[aas1611]

I have a database field that contains names (data type varchar). These names are in uppercase, and I want to make them nice, like the first letter of every word stays in uppercase and the rest in lower case.

For example, instead of 'JOHN WILLIAMS DOE', it has to be 'John Williams Doe'.

Is it possible?

Thanks!

 

[KarveR]

It is possible. You just need to be determined.

Very determined.

How many records did you say you had?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

It could be done with SQL, but it would be so complicated it wouldn't be worth it. You really need a program to do it.

 

[SQLDenis]

What about regular expressions in MySQL?

$string =~ s/\b(\w)/uc($1)/eg Capitalize first letter of all words in a string

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[TonyGroves]

That's not MySQL; it's Perl or PHP or whatever. MySQL supports regular expressions, but only for finding patterns, not for substitution.

 

[aas1611]

Well, so I guess there is no an easy and fast solution in MySQL, huh?

The code form SQLDenis, that's PHP, isn't it? I will try it then.

By the way, I have 6000 records need to be changed, I am not THAT determined.

 

[TonyGroves]

Well, if you really wanted to use SQL, one system you could use would be: (1) convert the name to lowercase, (2) put a space in front of the name, (3) change all occurrences of " a" to " A", " b" to " B", etc., (4) strip the leading space. Not that complicated I suppose, but a lot of code.

For example:
[tt]
UPDATE tblname
SET
name=
SUBSTRING(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
CONCAT(' ',LOWER(name)),
' a',' A'),
' b',' B'),
' c',' C'),
' d',' D'),
' e',' E'),
' f',' F'),
' g',' G'),
' h',' H'),
' i',' I'),
' j',' J'),
' k',' K'),
' l',' L'),
' m',' M'),
' n',' N'),
' o',' O'),
' p',' P'),
' q',' Q'),
' r',' R'),
' s',' S'),
' t',' T'),
' u',' U'),
' v',' V'),
' w',' W'),
' x',' X'),
' y',' Y'),
' z',' Z'),
2
)
[/tt]

 

[r937]

tony, great code, but it doesn't handle everything

for example O'TOOLE becomes O'toole, and it should be O'Toole

aas1611, no matter how you do it, you will still have to eyeball the results

names are notoriously difficult to parse



r937.com | rudy.ca

 

[edpatterson]

Personally, I promote everything to uppercase prior to storing it then use what every I am quering the data out with (perl or php) to make it look pretty. My logic is anyone with access to the raw data is not going to care about how it is stored, people viewing the results of queries will not know the difference.

<tire comercial knock off>
data aitn't pretty
</tire comercial knock off>

my .02 worth.

Ed

 

[r937]

people viewing the results of queries will not know the difference

ed, this may come as a surprise, but if my name is O'Toole and i enter it as O'Toole and you show it back to me as O'toole, dude, i am so out of your site...




r937.com | rudy.ca

 

[edpatterson]

You must not have read my entire post

use what every I am quering the data out with (perl or php) to make it look pretty.

As a user you would never know how I store your name.

Ed

 

[r937]

fine, point taken, but if you store names in all caps (even if you don't tell me), then how do you know it wasn't entered as O'toole? what's your logic for recovering the data that you have destroyed?

i guess the better example is MacDonald and Macdonald -- those are two different valid spellings, two different names, and you better not mix them up!!

r937.com | rudy.ca

 

[TonyGroves]

Rudy's right about names; it's almost impossible to cater for the enormous variety out there. Apart from his example of O'Toole, you've also stuff like McDonald, Smith-Jones, Ó hEadhra, Mac an Ultaigh (both from my part of the world), and so on. Just make sure it's stored properly in the first place, because if you discard case information, you're never going to get it back with 100% reliability, and your applications end up looking semi-literate and offending people.

 

[aas1611]

Thanks guys for all your hints and discussions. I've got my name list from an old database. Next time I have to make sure that names are entered properly at the first place.

Tony, I will give your code a try. It looks promising and I will let you know the result.

Thanks again!

 

[aas1611]

Tony, your code worked pretty good to solve my problem. Like been discussed before, I still have to put eyes on some records.

Thanks!