Salim berbagi…. tempat belajar dan berinteraksi….


Function String Tekonizer
January 26, 2010, 2:17 AM
Filed under: ORACLE

Function ini digunakan untuk mendapatkan nilai/value ‘kata’ ke ‘sekian’ dari sederet data berupa kalimat atau string. Dengan parameter:
– Data string
– Posisi ke ‘berapa’ yang akan diambil
– Pemisah atau separator dari data string

Contoh:
Syntax : select string_tokenizer(‘Smile on your face’, 3, ‘ ‘) from dual;
Hasilnya: your

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

CREATE OR REPLACE FUNCTION      string_tokenizer (
   p_data        IN   VARCHAR2,
   p_pos         IN   PLS_INTEGER,
   p_separator   IN   VARCHAR2 DEFAULT ‘.’
)
   RETURN VARCHAR2
IS
   l_data   VARCHAR2 (32767) := p_separator || p_data;
   l_i1     PLS_INTEGER;
   l_i2     PLS_INTEGER;
BEGIN
   l_i1 := INSTR (l_data, p_separator, 1, p_pos);

   IF l_i1 > 0
   THEN
      l_i2 := INSTR (l_data, p_separator, 1, p_pos + 1);

      IF l_i2 = 0
      THEN
         l_i2 := LENGTH (l_data) + 1;
      END IF;

      RETURN (SUBSTR (l_data, l_i1 + 1, l_i2 – l_i1 – 1));
   ELSE
      RETURN NULL;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END string_tokenizer;
/

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


Leave a Comment so far
Leave a comment



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



%d bloggers like this: