Reputation: 184
Oracle SQL: Return text in string based on search of string
The following is an example of the text I want to create a function to query against:
"!PRINTSERVER.PAR
$MODE=QUIET
$DEBUG=N
$LOG_FILE=[file path]
$PRINTER_LIST=
-ACCOUNTS_LASER,\\print02\Accounts_Laser,winspool,Ne34:
-BE_PRINTER01,\\print01\BE_Printer01,winspool,Ne03:
-CUSTSERV_PRINTER,\\print01\CS_Laser,winspool,Ne06:
As an 'in' parameter of my function I want to search for the logical printer name, e.g. ACCOUNTS_LASER and I would like it to return the physical path e.g. \\print02\Accounts_Laser.
Also, the field in question that contains the aforementioned text has a data type of long, therefore I believe it requires converting to a string before any Oracle functions can be applied to it.
I would guess that I need a combination of substr and instr or maybe regexp however any assistance would be much appreciated.
Upvotes: 4
Views: 1935
Answers (4)
Reputation: 184
Thanks to everyone who posted suggestions. I have used some of the sql in the answers below to create a function that solves my issue.
create or replace function get_printer_path (l_printer_name in varchar2)
return varchar2
is
p_printer_path varchar2(5000);
cursor temp is
select
regexp_replace(dbms_xmlgen.getxmltype('select info from print_server where
server_name = ''STAGING'''), '.*-'|| l_printer_name ||',([^,]*),.*', '\1', 1, 1, 'n')
from dual;
begin
open temp;
fetch temp into p_printer_path;
if (p_printer_path not like '\\%') then
p_printer_path := null;
end if;
close temp;
return p_printer_path;
end get_printer_path;
Any further enhancements or if I am breaking any standard practices then please continue to comment.
Upvotes: 1
Reputation: 43013
Regex
'^(.*?' || 'ACCOUNTS_LASER' || ',)([^,]+)(.*)$'
Flags
n
Description
^(.*?<Printer name goes here>',)([^,]+)(.*)$
Recipe
-- INIT
create table test (input clob);
insert into test(input) values('
"!PRINTSERVER.PAR
$MODE=QUIET
$DEBUG=N
$LOG_FILE=[file path]
$PRINTER_LIST=
-ACCOUNTS_LASER,\\print02\Accounts_Laser,winspool,Ne34:
-BE_PRINTER01,\\print01\BE_Printer01,winspool,Ne03:
-CUSTSERV_PRINTER,\\print01\CS_Laser,winspool,Ne06:
');
-- SELECT
select
regexp_replace(input, '^(.*?' || 'ACCOUNTS_LASER' || ',)([^,]+)(.*)$','\2', 1, 0, 'n') printer_path
from
test
union all
select
regexp_replace(input, '^(.*?' || 'BE_PRINTER01' || ',)([^,]+)(.*)$','\2', 1, 0, 'n') printer_path
from
test
union all
select
regexp_replace(input, '^(.*?' || 'CUSTSERV_PRINTER' || ',)([^,]+)(.*)$','\2', 1, 0, 'n') printer_path
from
test
outputs
|PRINTER_PATH |
|--------------------------|
| \\print02\Accounts_Laser |
| \\print01\BE_Printer01 |
| \\print01\CS_Laser |
Upvotes: 3
Reputation: 1456
You're dealing with two completely independent problems. The first is this:
the field in question that contains the aforementioned text has a data type of long
LONG is deprecated and Oracle recommends converting to CLOB if at all possible, and gives extensive documenation and support for scrubbing them from your database: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_long_lob.htm#ADLOB008
I'll assume that's not an option, because this is life, and it never is. If you are guaranteed that your LONG column will be less than 32k characters, you can fetch it into a PL/SQL VARCHAR2 string of up to 32k, and extract your printer path with a regular expression
ETA: NOTE - Code below will get an error if your long is > 32k... L_ROW.STR is actually a hidden VARCHAR2(32676)
create table t_long (str long);
insert into t_long values (
'"!PRINTSERVER.PAR
$MODE=QUIET
$DEBUG=N
$LOG_FILE=[file path]
$PRINTER_LIST=
-ACCOUNTS_LASER,\\print02\Accounts_Laser,winspool,Ne34:
-BE_PRINTER01,\\print01\BE_Printer01,winspool,Ne03:
-CUSTSERV_PRINTER,\\print01\CS_Laser,winspool,Ne06:'
);
CREATE OR REPLACE FUNCTION GET_PRINTER_PATH (P_PRINTER_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
L_RESULT VARCHAR2(4000);
BEGIN
FOR L_ROW IN (SELECT STR FROM T_LONG) LOOP
L_RESULT:= REPLACE(REGEXP_SUBSTR(L_ROW.STR,'(^-'||P_PRINTER_NAME||',)([^,]*)',1,1,'m'),'-'||P_PRINTER_NAME||',');
IF L_RESULT IS NOT NULL THEN
RETURN L_RESULT;
END IF;
END LOOP;
RETURN NULL;
END;
WITH SAMPLE_NAMES AS(
SELECT 'ACCOUNTS_LASER' PRINTER_NAME FROM DUAL UNION ALL
SELECT 'BE_PRINTER01' PRINTER_NAME FROM DUAL UNION ALL
SELECT 'CUSTSERV_PRINTER' PRINTER_NAME FROM DUAL UNION ALL
SELECT 'DUMMY1' PRINTER_NAME FROM DUAL)
SELECT PRINTER_NAME, GET_PRINTER_PATH (PRINTER_NAME) PRINTER_PATH FROM SAMPLE_NAMES;
PRINTER_NAME PRINTER_PATH
ACCOUNTS_LASER \\print02\Accounts_Laser
BE_PRINTER01 \\print01\BE_Printer01
CUSTSERV_PRINTER \\print01\CS_Laser
DUMMY1
If you will have strings longer than 32k, you'll probably want to go with a 2-step process: (1) create a Global Temporary Table (GTT) that uses a CLOB and (2) write a function similar to above that first puts data into the GTT and then uses Regexp against the CLOB:
CREATE OR REPLACE FUNCTION GET_PRINTER_PATH_CLOB (P_PRINTER_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO T_CLOB
SELECT TO_LOB(STR) FROM T_LONG;
FOR L_ROW IN (SELECT REPLACE(REGEXP_SUBSTR(STR,'(^-'||P_PRINTER_NAME||',)([^,]*)',1,1,'m'),'-'||P_PRINTER_NAME||',') PRINTER_PATH
FROM T_CLOB)
LOOP
IF L_ROW.PRINTER_PATH IS NOT NULL THEN
COMMIT;
RETURN L_ROW.PRINTER_PATH;
END IF;
END LOOP;
COMMIT;
RETURN NULL;
END;
Upvotes: 0
Reputation: 5820
I obviously don't know your real requirements, but assuming the data is stored in a table called test_tbl, something like this could get you on the right track:
DECLARE
FUNCTION printer_path(printer_name_in IN VARCHAR2)
RETURN VARCHAR2
IS
v_retval VARCHAR2(1000);
BEGIN
FOR rec IN (select col from test_tbl)
LOOP
IF regexp_like(rec.col, '.*-'||printer_name_in||',([^,]*),.*', 'n')
THEN
v_retval := regexp_replace(rec.col, '.*-'||printer_name_in||',([^,]*),.*', '\1', 1, 1, 'n');
EXIT; -- exit loop if data found (not sure what your real requirements are)
END IF;
END LOOP;
RETURN v_retval;
END printer_path;
BEGIN
dbms_output.put_line('Path: '||printer_path('ACCOUNTS_LASER'));
END;
Breaking down the regexp_ functions:
- Match any character, followed by a "-", followed by the passed in name, followed by a comma, followed by anything that isn't a comma, followed by a comma, followed by any character
- The parenthesis in ([^,]*) grabs that information to be able to use in a backreference later
- the 'n' as the match parameter just lets "." match the new line character.
I hope this helps, but be sure to test a lot of scenarios as I have no idea what your data might actually look like!
Upvotes: 0
Related Questions
- Returning a specific substring from ORACLE SQL string using REGEXP_SUBSTR
- Extract a substring from a field PLSQL
- Fetch Substring in Oracle
- How to match regular expression and return a substring of the match
- Retrieving certain strings within a string using Oracle SQL - Part 2
- Retrieving certain strings within a string using Oracle SQL
- How to return a substring from a text in Oracle
- PL SQL regular expression substring
- How to extract substrings from strings in Oracle with PL/SQL?
- Search for substring, return another substring