How to make first letter of every word as capital letter in SAP HANA

Question

Do we have any method to make first letter of every word as capital letter for a column in SAP HANA?

i.e "ask question" to "Ask Question"

Answer 1

With HANA 2 SPS3, there is a built in function called "Initcap"

SELECT INITCAP('that''s a new function') FROM DUMMY;

That'S A New Function

Answer 2

Possibly this site gives you a clue? I'm not familiar with SAP HANA but possibly you can label it as title sentence and do a case normalisation on it?

In T-SQL you could do something like this:

SELECT
UPPER(LEFT(ColumnA,1))+LOWER(RIGHT(ColumnA,(LEN(ColumnA)-1)))
FROM Table1;

You tell the DBMS to make the first letter in upper case, and the rest of the string in lower case. The LEN is needed because I assume the length of the string is not static and in the way of the script it is handled dynamically. The + is CONCAT in other dialects. Regarding whitespaces in ColumnA and the capitalization of the 2nd or more word in you column, I'm trying some stuff that Michael Valentine Jones described on 1-12-2006 here, alsee see part of the code beneath. I get back to this as soon as I figured this out. I'm not sure yet how the union all select statement works. Will take some days (due to the weekend). Anyway, maybe this helps you. Change the REPLACE of ',' with ' ' or something.

-- Create temp table to test inserting values into
create table #t (num int)

-- Create a comma delimited string to test with
declare @str    varchar(500)
select @str = '4,2,7,7834,45,24,45,77'

--------------------------------------------------------
---- Code to load the delimited string into a table ----
--------------------------------------------------------

-- Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = 'insert into #t select '+ replace(@str,',',' union all select ')

-- Load values from comma delimited string into a table
exec ( @sql )

Answer 3

There is no builtin function for that on SQL level. You could however write a user-defined function to do just that.

Answer 4

I found a piece of T-SQL script that creates a function 'ProperCase' that will do the trick by calling the function in your select statement. See beneath. I foun it here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718 (by jsmith8858).

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);

   select @Reset = 1, @i=1, @Ret = '';

   while (@i <= len(@Text))
    select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end

And for example if you do a SELECT dbo.ProperCase ('ask question WHy iS tHAT'), then you get 'Ask Question Why Is That' as a result.

Hopefully this gets you on your way.