Change all city names to proper case

[stopsusa]

I am trying to change all city fields to proper case ie: san francisco to San Francisco. I can only find functions UCASE and LCASE but none for proper case.

I tried without success:
UPDATE table1 SET substring(city,1,1) = UCASE(substring(country,1,1))

Any suggestions on this?

 

[sleipnir214]

I say write a program in your preferred programming language to do it.

It may be possible to do it in a SQL query, but with the possibility of more than one word in a city name, it's going to be a tought one to write. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[tankma9]

use php or perl to create a program like this it will make it easier and these languages are powerful enough to get the job done and if you dont know either of these use java.

 

[likeopensource]

Not exactly what you want, this is an example of creating proper case in PHP

function convert2proper($strVal){
$iPos = 0;
$strValTmp = "";
do {
$iSpace = strpos($strVal," ", $iPos);
$strValTmp .= strtoupper(substr($strVal, $iPos, 1));
$strValTmp .= strtolower(substr($strVal, $iPos + 1, $iSpace - $iPos));
$iPos = $iSpace + 1;
} while(strpos($strVal," ", $iPos) != FALSE);
$strValTmp .= strtoupper(substr($strVal, $iPos, 1));
$strValTmp .= strtolower(substr($strVal, $iPos + 1));
return $strValTmp;
}
function cutString($strVal,$limit){
if(strlen($strVal)>$limit){
return substr($strVal,0,$limit) . "...";
}else{
return $strVal;
}
}



These are 2 functions that are very useful in search engines and filling select boxes where you don't want the select box to be wider than a certain character length. the first one converts strings to proper case, with the beginning of every word in caps. the second will cut text at a certain length and will put "..." at the end.

Use is pretty self explanitory.

convert2proper("HI HOW ARE YOU?&quot returns "Hi How Are You?"


It is possible to get proper case in PostGresql as well