CoderZone.org

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)

Author: CoderZone
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)
 
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 4