|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/29/2008 5:40:04 PM
Posts: 7,
Visits: 11
|
|
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
T-Nguyen
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
Less dirty:
install the availabe Oracle UDF library and simply run.
select
initcap(cityname)
from tab
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
Less dirty:
install the availabe Oracle UDF library and simply run.
select
initcap(cityname)
from tab
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/29/2008 5:40:04 PM
Posts: 7,
Visits: 11
|
|
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,
T-Nguyen
|
|
|
|