SQL & PL/SQL

Hi All,

Hope all are doing great.. its always fun with this forum and had spent quality time here reading/expressing views on the forum. Keep up the good spirit all volunteers.

I would recommend you to proceed reading the below discussion only when you have some free time as this is not super urgent issue and just for FUN !!!

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Thinking about an built-in function for converting string into binary format. My search in the documentation was unsuccessful or may be I did not pay much attention (please point me to a place where I can find any built-in functionality which can help achieve that.)

If you think I am following wrong approach and doing something like below figure then please point me to correct direction (built in function).

But meanwhile I thought it would be simple thing, break the string and get the ascii values convert into binary format that's all..

Oracle version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

WITH string_tokenizer (token_order,

                   k,

                   rem\_val,

                   r)

 AS (SELECT token\_order,

            str\_split k,

            0 rem\_val,

            8 r

       FROM (    SELECT ROW\_NUMBER () OVER (ORDER BY NULL) token\_order,

                        ASCII (SUBSTR ('&&str', ROWNUM, 1)) str\_split

                   FROM DUAL

             CONNECT BY ROWNUM \<= LENGTH ('&&str')) dataset

     UNION ALL

     SELECT token\_order,

            TRUNC (k / 2),

            MOD (k, 2) rem\_val,

            r - 1

       FROM string\_tokenizer

      WHERE r > 1)

SELECT LISTAGG (rem_val)

      WITHIN GROUP (ORDER BY token\_order, REPLACE (r, 8, 0) ASC)

      string\_to\_binary

FROM string_tokenizer;

There is a website as well which helps in converting this (not posting as it may violate the rules of the forums .. search in google and help yourself)

Example :

Input : 'Good morning!'

Output : 01000111011011110110111101100100001000000110110101101111011100100110111001101001011011100110011100100001

Problem & questions related to above snippet :

===================================

Please suggest better ways to achieve this for my learning. Post your version (preferably in SQL).

I don't have any benchmark to set for performance so any other solutions which can be faster than this are welcome for me to learn.

Is there a way if we can remove the connect by clause as it makes code ugly.

Thanks for reading & good day !

Cheers,

Manik.