select only first letters of words from a varchar field.

Question

Hi,

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

Maybe it would involve using the function substr?

Answer 2

The answer is using REGEX_SUBSTR.

See the docs here, and a close example here.

Answer 3

You could use the split function described here (replacing the comma by a space), in order to split the sentence by its spaces. Then, you could use the substr function as AJ says, because the result of the split would allow you to start from char 1 to char 2 of every "piece".

It involves substr after all, right??

PS. I would rather process the result in a layer above, not during the query. But that's me.

Answer 4

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.