InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 15:34:55 GMT20http://www.teradata.com/teradataforum/Topic10829-5-1.aspxThank you, we are using Teradata and does not have Oracle package to be install. do you have another way to simplify the process?thanks,Tue, 11 Mar 2008 18:07:45 GMTnguyent02http://www.teradata.com/teradataforum/Topic10829-5-1.aspxLess dirty:install the availabe Oracle UDF library and simply run.select initcap(cityname)from tabDieterFri, 07 Mar 2008 07:19:52 GMTdnoethhttp://www.teradata.com/teradataforum/Topic10829-5-1.aspxLess dirty:install the availabe Oracle UDF library and simply run.select initcap(cityname)from tabFri, 07 Mar 2008 07:19:30 GMTdnoethhttp://www.teradata.com/teradataforum/Topic10829-5-1.aspxOne sample dirty tick is this one ....CREATE TABLE DATA004(CITYNAME VARCHAR(30));INSERT INTO DATA004 VALUES('WEST MINISTER');INSERT INTO DATA004 VALUES('COSTA MESA CITY');INSERT INTO DATA004 VALUES('');INSERT INTO DATA004 VALUES('A');INSERT INTO DATA004 VALUES('BC');INSERT INTO DATA004 VALUES('D E');INSERT INTO DATA004 VALUES(NULL);WITH RECURSIVE GETCITYNME(ORIG_NAME, CITY_NAME, CURPOS, LVL)AS(SELECT CITYNAME , CASE WHEN CHARACTER_LENGTH(CITYNAME) > 0 THEN SUBSTRING(CITYNAME FROM 1 FOR 1) || SUBSTRING( LOWER(CITYNAME) FROM 2) ELSE CITYNAME END , POSITION(' ' IN CITYNAME) CPOS , 0FROM DATA004UNION ALLSELECT ORIG_NAME , SUBSTRING(CITY_NAME FROM 1 FOR CURPOS) || UPPER(SUBSTRING(CITY_NAME FROM CURPOS+1 FOR 1)) || SUBSTRING(CITY_NAME FROM CURPOS+2) NEW_NME , NULLIF(POSITION(' ' IN SUBSTRING(CITY_NAME FROM CURPOS+1))-1, 0 )+1+CURPOS CPOS , LVL + 1 FROM GETCITYNMEWHERE NEW_NME(CS) <> CITY_NAME(CS))SELECT CITY_NAMEFROM GETCITYNMEQUALIFY RANK() OVER (PARTITION BY CITY_NAME ORDER BY LVL DESC) = 1; CITY_NAME ? A Bc Costa Mesa City D E West MinisterThu, 06 Mar 2008 23:08:53 GMTjoedsilvahttp://www.teradata.com/teradataforum/Topic10829-5-1.aspxI need your help so badly, i have a requirement to convert text into proper case such as following:RAW data City Name to City NameWESTMINSTER CITY Westminster City (Upper case W and C)COSTA MESA CITY Costa Mesa City (Upper C, M, and C).Please teach me some trick here.... please.... PleseThu, 06 Mar 2008 19:55:08 GMTnguyent02