Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: SUBST and INSTR together Jump to:  
Category: >> Oracle PL/SQL >> SUBST and INSTR together Bookmark and Share

<< lastnext >>

Snippet Name: SUBST and INSTR together

Description: Using SUBST and INSTR together allow you to do complex extractions of characters from strings.

Also see:
» FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: with parameters
» FUNCTIONS: without parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» IN Function
» Built-In Functions: CASE
» Built-In Functions: DECODE
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL
» Date Functions: NUMTODSINTERVAL

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009

-- String parsing using both SUBSTR And INSTR 
 
 
- get ALL characters up TO the FIRST comma
SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1)-1)
FROM dual;
 
 
-- get the character(s) in between the commas
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;
 
 
-- get the character(s) after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual; 
 
 
-- find the first blank from the right 
-- the -1 parameter of INSTR indicates that we're searching 
-- for the first occurrence going backwards from the end of 
-- the column.
 
     SELECT SUBSTR(name
                 ,INSTR(name,' ',-1)+1
                 ) AS surname
     FROM test_table
 
 
 
-- a PL/SQL function that takes two strings representing a 
-- list of numbers separated by commas and returns a string 
-- representing the list of each nth element added together.
 
CREATE OR REPLACE FUNCTION test_func(p_arg1 VARCHAR2, p_arg2 VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
 IF ( INSTR(p_arg1,',') = 0 AND INSTR(p_arg2,',') = 0 ) THEN
  RETURN TO_NUMBER(p_arg1) + TO_NUMBER(p_arg2);
 ELSIF (INSTR(p_arg1,',') = 0 OR INSTR(p_arg2,',') = 0) THEN
  raise_application_error(-20001, 'Length of the strings are not equal');
 ELSE
  RETURN TO_CHAR(TO_NUMBER(SUBSTR(p_arg1, 1, INSTR(p_arg1,',') - 1)) + TO_NUMBER(SUBSTR(p_arg2, 1, INSTR(p_arg2,',') - 1)))
         ||','||
         test_func(SUBSTR(p_arg1, INSTR(p_arg1,',') + 1 ), SUBSTR(p_arg2, INSTR(p_arg2,',') + 1 ));
 END IF;
END;
/
 
 
 
-- the Linux command "basename" is most famous for taking a 
-- full file path string and stripping away the leading path 
-- component, returning just the name of the file. This can 
-- be emulated in PL/SQL with calls to SUBSTR and INSTR, 
-- like this:
 
SUBSTR(dirname,INSTR(dirname,'/',-1)+1)
 
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 185 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?