select only first letters of words from a varchar field

oracle, sql, sqlplus

Solution

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.

Problem

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 ```

Original source