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
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
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.
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.
' teST IS a TEst '
The intcap function is super.
Are there other function slike this new in 14?
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 ......