initcap

[ayushchd]

In one of the fields of my database, I have text as

ABC ABC

I want to convert all to

Abc Abc

Is there any function to do so? like INITCAP()?

[ZydoSEO]

I'm assuming this is MySQL?

[ayushchd]

Yes. Is there a function like INITCAP() in mysql?

[Demaestro]

UPDATE <table>
SET <textfield> = UPPER(LEFT(<textfield>,1)) +
LOWER(SUBSTRING(<textfield>,2))

Something like this maybe?

[ZydoSEO]

I believe that will only get the first occurance in the string. I believe if the string has 20 words, he wants each of the words UCASE'd.

There is no MySQL built in function for this that I can find. I have been trying to figure out how to do this for the last half hour LOL (bored at work).

I was thinking you could first LOWERCASE the entire string and then cycle through the string UPPERCASE'ing the first character of every word using a regular expression that would match 1) the very first character of the entire string (gets the first word) and 2) any match on a space followed by an alphabetic character. I'm not sure you can do this in SQL but surely it would be relatively simple to do in PhP.

Now after thinking about it, depending on the data, LOWERCASE'ing the entire string first might cause different problems. What if acronyms or abbreviations are located in the string? You'd probably NOT want to convert USA or CPU to Usa and Cpu respectively. So a lot of what you do and how you approach this is probably going to depend on your knowledge of the data itself.

It would probably be better to NOT LOWERCASE the string first. Simply cycle through the original string UPPERCASE'ing the first character of every word using a regular expression that would match 1) the very first character of the entire string (gets the first word) and 2) any match on a space followed by an already lowercase alphabetic character.

[edited by: ZydoSEO at 8:20 pm (utc) on Nov. 28, 2007]

[Demaestro]

ZydoSEO you are right... I kinda slapped that together.

Doesn't PHP have a function that does this?

If so instead of changing the values in the database you can just alter them at time of display.

So everytime you call a value from that field onto the page you run it through a function that would do that job of casing it.

So instead of.....

<php echo $field_name

it would be .....

<php echo INITCAP($field_name)

Or what ever the function name is... I don't use PHP so I don't know but if you don't output the field in too many places that might be the easier way to go.

Also if you do it this way if more people write "bad" cased letters to that field it won't matter because it will get handled at time of display.

In fact if I remember correctly this is the way it should be done.

Formatting should be a display thing not a DB thing.

[edited by: Demaestro at 8:28 pm (utc) on Nov. 28, 2007]

[bcolflesh]

[us2.php.net...]

[ZydoSEO]

Problem solved! I looked right over that function. I read it as UPPERCASE'ing the 1st character of the first word in a string.