Saturday, 15 November 2014

Query to Split Space Delimiter String in Oracle

Step 1: Create table

CREATE TABLE DELIMETER( NAME VARCHAR2(100 BYTE));


Step 2: Insert data into the column having space delimiter

INSERT INTO DELIMETER (NAME) VALUES ('Elangovan Ragavan')

INSERT INTO DELIMETER (NAME) VALUES ('Dhana Sekaran')

INSERT INTO DELIMETER (NAME) VALUES ('AjithaAngel Anantharaj')


 Step 3: Run the below Query to split the name base on space delimiter

/* Formatted on 11/15/2014 11:08:51 AM (QP5 v5.115.810.9015) */
SELECT   SUBSTR (NAME, 1, STRING_POS - 1) AS FIRST_NAME,
         SUBSTR (NAME, STRING_POS, LENGTH (NAME)) AS LAST_NAME
  FROM   (SELECT   T.NAME, INSTR (T.NAME, ' ') AS STRING_POS
            FROM   DELIMETER t)


 
 Output:-


FIRST_NAME  LAST_NAME
Elangovan                  Ragavan
Dhana  Sekaran
AjithaAngel  Anantharaj









 









3 comments:

  1. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again,
    Regards, obiee training in hyderabad

    ReplyDelete
  2. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again,
    Regards, obiee training in hyderabad

    ReplyDelete
  3. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete