All Forums Analytics
nguyent02 8 posts Joined 03/07
06 Mar 2008
Convert Text to Proper Case is huge problme

I 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.... Plese

joedsilva 505 posts Joined 07/05
06 Mar 2008

One 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 Minister

dnoeth 4628 posts Joined 11/04
07 Mar 2008

Less dirty:install the availabe Oracle UDF library and simply run.select initcap(cityname)from tab

Dieter

dnoeth 4628 posts Joined 11/04
07 Mar 2008

Less dirty:install the availabe Oracle UDF library and simply run.select initcap(cityname)from tabDieter

Dieter

nguyent02 8 posts Joined 03/07
11 Mar 2008

Thank you, we are using Teradata and does not have Oracle package to be install. do you have another way to simplify the process?thanks,

Teraditya 3 posts Joined 07/11
18 Dec 2012

I am working on a similar problem with First and Last names. I am using this in the view for formatting the last name -

UPPER(SUBSTR(FIRST_NAME,1,1)) ||   LOWER(SUBSTR(FIRST_NAME,2) )     AS FIRST_NAME

This works for one word last names such as SMITH which is now displayed as Smith, but names such as SMITH-VO are displayed as Smith-vo. I guess I might have to split such names based on hyphen and then Capitalize both split pieces. Do this for names like O'BRIEN as well with the apostrophe.
Anybody know of a better way to do this?

Teraditya 3 posts Joined 07/11
19 Dec 2012

I ended up doing this in the view to display Last Names graciously. Our email team can now use the data without having to spend time on formatting data every time.

 CASE 
  WHEN LAST_NAME LIKE '%-%' -- Last Names with hyphens in them - display as : Jones-Smith
     THEN SUBSTR(LAST_NAME,1,1) || LOWER(SUBSTR(LAST_NAME, 2, INDEX(Last_Name, '-') -2)) || '-' || 
            SUBSTR(LAST_NAME, INDEX(Last_Name, '-') +1,1) || LOWER(SUBSTR(LAST_NAME, INDEX(Last_Name, '-')+2))

  WHEN LAST_NAME LIKE '%''%'     -- Last Names with apostrophes in them - display as : O'Brien
     THEN SUBSTR(LAST_NAME,1,1) || LOWER(SUBSTR(LAST_NAME, 2, INDEX(Last_Name, '''') -2)) || '''' || 
           SUBSTR(LAST_NAME, INDEX(Last_Name, '''') +1,1) || LOWER(SUBSTR(LAST_NAME, INDEX(Last_Name, '''')+2))

  ELSE              -- Regular One Word Last Names
          SUBSTR(LAST_NAME,1,1) ||  LOWER(SUBSTR(LAST_NAME,2))  
  END AS LAST_NAME
amittera 35 posts Joined 12/09
11 Apr 2013

Hi ,
 
the above query is able to format the output upto two words in a string, its not searching for the second space.
column data as below before query run:
wrath of titans
serious hospital (disk) fnut 
 
Query :
Select
CASE
  WHEN col_name LIKE '% %' -- LAST NAMES WITH HYPHENS IN THEM - DISPLAY AS : JONES-SMITH
     THEN SUBSTR(col_name,1,1) || LOWER(SUBSTR(col_name, 2, INDEX(col_name, ' ') -2)) || ' ' ||
            SUBSTR(col_name, INDEX(col_name, ' ') +1,1) || LOWER(SUBSTR(col_name, INDEX(col_name, ' ')+2))
   ELSE              -- REGULAR ONE WORD LAST NAMES
          SUBSTR(col_name,1,1) ||  LOWER(SUBSTR(col_name,2)) 
END AS col_name
from database.table;
Data after query run as below:
Wrath Of titans
Serious Hospital (disk) fnut --- Third word is not changing to proper case.
 
Required data as below shud be:
Wrath Of Titans
Serious Hospital (Disk) Fnut.
 
Kindly suggest.

 

Amit Saxena
Teradata Consultant

dnoeth 4628 posts Joined 11/04
11 Apr 2013

Hi Amit,
in TD14 there's an INITAP function, before this is part of the exting Oracle UDFs.
Dieter

Dieter

amittera 35 posts Joined 12/09
12 Apr 2013

Thanks Dieter. We used TRIM number of times to get the desired result. We are on T12 :(

Amit Saxena
Teradata Consultant

12 Apr 2013

 

Hi Dieter,

Just asking out of curioisty that how to get and install that Oracle UDF package on a teradata

system ..and can a developer do that or admin rights would be required for that?

 

Also after installing , these functions would be accesible in ANSI or teradata mode?

 

Thanks in advance..

 

Cheers!

Nishant

 

You must sign in to leave a comment.