Convert Text to Proper Case is huge problme

Analytics
Enthusiast

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 Name
WESTMINSTER 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
10 REPLIES
Enthusiast

Re: Convert Text to Proper Case is huge problme

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
, 0
FROM DATA004

UNION ALL

SELECT 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 GETCITYNME
WHERE NEW_NME(CS) <> CITY_NAME(CS)

)
SELECT CITY_NAME
FROM GETCITYNME
QUALIFY RANK() OVER (PARTITION BY CITY_NAME ORDER BY LVL DESC) = 1
;

CITY_NAME
?

A
Bc
Costa Mesa City
D E
West Minister
Senior Apprentice

Re: Convert Text to Proper Case is huge problme

Less dirty:
install the availabe Oracle UDF library and simply run.

select
initcap(cityname)
from tab
Senior Apprentice

Re: Convert Text to Proper Case is huge problme

Less dirty:
install the availabe Oracle UDF library and simply run.

select
initcap(cityname)
from tab

Dieter
Enthusiast

Re: Convert Text to Proper Case is huge problme

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,

Re: Convert Text to Proper Case is huge problme

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?

Re: Convert Text to Proper Case is huge problme

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

Enthusiast

Re: Convert Text to Proper Case is huge problme

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.

Senior Apprentice

Re: Convert Text to Proper Case is huge problme

Hi Amit,

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

Dieter

Enthusiast

Re: Convert Text to Proper Case is huge problme

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