Practice patience to feel the SQL’s power…

Few days back one of my peers went through a recruitment interview. He was given some SQL challenges to solve and he solved all except the following one:

 

SQL> select emp_name

  2  from employee;

 

EMP_NAME

————————————————————-

Rajesh Chakrabarti

 

 

He was told to show it like:

Rajesh

Chakrabarti

 

Somehow he was failed to solve this within the given time. Next day when he told me, we both tried it and solved it in different ways writing some clumsy code (containing more then 15 lines of PL/SQL code).

 

Then this technique came in mind…

 

SQL> select replace(emp_name,’ ‘,chr(10)) AS emp_name

  2  from employee;

 

EMP_NAME

————————————————————-

Rajesh

Charabarti

 

Simple solution, spaces are replaced by a Return [chr(10)].

 

 

The lesson we learned is: SQL’s power is sometimes ignored, we should think twice before moving toward PL/SQL.