REGEXP_REPLACE HELP String fist letter uppercase rest lowercase!

Database
Enthusiast

REGEXP_REPLACE HELP String fist letter uppercase rest lowercase!

I need to convert all words in a string to first letter uppercase rest lowercase, while skipping some specific words, and remove multiple spaces.

I like to convert: “ teST   is a TEst” to “Test is a Test”

This remove multiple spaces if found the

REEXP_REPLACE('  this    IS     a     Test  ', '( )+',' ',1,0,'c')

Were working, but the rest I have no idea how to do and looking around, on the net did not give much help either.

I need to do something like this: 

SELECT REGEXP_REPLACE(c.Str,'A,IS','a,is',1,0,'i') AS str -- This replaces single word> A to a, Is to is
FROM (
SELECT REGEXP_REPLACE(b.Str,'[^a-z]','[^A-Z]',1,0,'c') AS str -- This should set first letter to Upper and rest to lover case>: This Is A Test
FROM (
SELECT TRIM(REGEXP_REPLACE(' this IS a Test ', '( )+',' ',1,0,'c')) AS str -- removes multiple Spaces and trim
) AS b
) AS c

Or if possible then all this combined in one REGEXP would also be nice.

Peter Schwennesen

Tags (1)
3 REPLIES
Enthusiast

Re: REGEXP_REPLACE HELP String fist letter uppercase rest lowercase!

I see I like to convert: “ teST   is a TEst” to “Test is a Test”. The last word again starts with upper case.

I put an initcap to the result you got above. Then I replace, Is with is and A with a.

select 

regexp_replace(regexp_replace(initcap(REGEXP_REPLACE('  teST    IS     a     TEst  ', '( )+',' ',1,0,'c')) ,'Is','is',1,1,'i'),'A','a',1,1,'i)

Enthusiast

Re: REGEXP_REPLACE HELP String fist letter uppercase rest lowercase!

Hi Raja

The intcap function is super.

Are there other function slike this new in 14?

Peter Schwennesen

Enthusiast

Re: REGEXP_REPLACE HELP String fist letter uppercase rest lowercase!

Many oracle   functions are available in Teradata 14. You can check it out. In TD 15, you will be amazed to know about various array, varray functions like oracle, aggregate functions,arithmetic,trigo, hyperbolic functions, array, varray functions like oracle, built-in functions,calendar functions,lob functions ......