Select only the first letters of words from the varchar field

Question

I was asked in an interview, a question from the oracle sql. This seemed like a simple question, but I had no idea to answer. Can anybody help?

if there is a line in the column, such as "newyork is a beautiful city."

select column_name from table_name;

will result in

newyork is a beautiful city

what is the query needed to output as a string with all the first letters. that is, the output should be

niabc

Answer 1

If you don't care about saving the output, this can be done 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 numbers, punctuation marks, etc., we would have to add these characters to the first replacement line, which could become quite tedious.

Answer 2

REGEX_SUBSTR.

docs , .

Answer 3

split, ( ), . substr, AJ, char 1 char 2 "".

, substr, ?

PS. , . .

Answer 4

, substr?