Reputation: 67231

select only first letters of words from a varchar field

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

Upvotes: 5

Answers (4)

APC

Reputation: 146239

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.

Upvotes: 7

Alfabravo

Reputation: 7569

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.

Upvotes: 2