select only first letters of words from a varchar field

Question

I was asked in an interview,a question from oracle sql.this seemed to be a simple question but i had no clue to answer.could anybody help?

if there is string like "newyork is a beautiful city" in a colum.

select column_name from table_name;

will result

newyork is a beautiful city

what is the query required to give the output as a string with all the first letters. i.e., the output should be

niabc

Answer 1

Provided you're not concerned with maintaining the case of the output this can be done quite simply without the need for recursion:

SQL> select
  2      translate(
  3            initcap('newyork is a BEAUTIFUL city')
  4               , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'
  5               , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
  6              )
  7  from dual
  8  /

TRANS
-----
NIABC

SQL>

If the sentence contains numerals, punctuation, etc then we would have to add those characters to the first replacement string, which could get rather tedious.