ramesh_73
asked on
Substring based on a delimiter
I have the string structure as "l0adad@15-apr-1999@succes s@select count(*) from dual"
In this I need to extract string separated by the delimiter '@'. For example in this case I need to extract l0adad
15-apr-1999
success
and select count(*) from dual
into separate variables. Could anyone help me in suggesting a way for this. Do you have any direct functions for doing this?
In this I need to extract string separated by the delimiter '@'. For example in this case I need to extract l0adad
15-apr-1999
success
and select count(*) from dual
into separate variables. Could anyone help me in suggesting a way for this. Do you have any direct functions for doing this?
a smple way to do it is :
ix := 1;
start := 1;
stop := 1;
vc := "l0adad@15-apr-1999@succes s@select count(*) from dual"
;
while TRUE loop
IF SUBSTR(vc,start,stop) IN ('@') then
vString := SUBSTR(vc,ix,stop)
start := ix;
END IF;
ix := ix + 1;
stop := ix;
end loop
something like this... in vString you receive for each time you get a "@" the hole sub-string. you can put this substrings in an array for save them...
Hope this helps
ix := 1;
start := 1;
stop := 1;
vc := "l0adad@15-apr-1999@succes
;
while TRUE loop
IF SUBSTR(vc,start,stop) IN ('@') then
vString := SUBSTR(vc,ix,stop)
start := ix;
END IF;
ix := ix + 1;
stop := ix;
end loop
something like this... in vString you receive for each time you get a "@" the hole sub-string. you can put this substrings in an array for save them...
Hope this helps
opppsss! I miss the condition to exit from the Loop... this is when the ix gets to LENGTH(vc)...
and I think you hace to replace start for ix in the IF condition...
well the point is giving you an idea...
:)
and I think you hace to replace start for ix in the IF condition...
well the point is giving you an idea...
:)
you could also try using instring function to find the position of '@' in the string and then use the substr function to extract.
Below is a package I wrote while ago to parse delimited strings:
-- __________________________ __________ __________ __________ __________ ____
-- | |
-- | PARSING_PKG |
-- |_________________________ __________ __________ __________ __________ ___|
-- FILE: PARSING.PKG
-- LOCATION:
-- TITLE: Parsing Package
-- TYPE: ORACLE PL/SQL Package
-- VERSION: 1.0
-- CREATED: June 25, 1996
-- AUTHOR: Solomon Yakobson
-- WARNING:
-- DESCRIPTION: This package will provide all components necessary to
-- perform various data parsing routines.
-- NOTES:
-- MODIFICATION
-- HISTORY:
-- __________________________ __________ __________ __________ __________ ___
CREATE OR REPLACE PACKAGE Parsing_Pkg
AS
FUNCTION List_Item_Count(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ','
) RETURN NUMBER;
FUNCTION Get_List_Item(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ',',
nItemNumber IN NUMBER
) RETURN VARCHAR2;
END Parsing_Pkg;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY Parsing_Pkg
AS
-- __________________________ __________ __________ __________ __________ ___
-- | |
-- | LIST ITEM COUNT |
-- |_________________________ __________ __________ __________ __________ ___|
-- TITLE: List Item Count Function
-- TYPE: Public Packaged Fuction
-- VERSION: 1.0
-- CREATED: June 25, 1996
-- AUTHOR: Solomon Yakobson
-- WARNING:
-- DESCRIPTION: This function will parse a given list of items delimited
-- by a given delimiter and return number of items in that
-- list. List must have the following format:
--
-- <item_1><delimiter><item_2 ><delimite r>...<item _n>
--
-- List can not be empty, can not start with list item
-- delimiter and can not have two list item delimiters next
-- to each other. Delimiter is a character string of any
-- length. If omitted, delimiter defaults to ','.
-- PARAMETERS: This function requires two input parameters: list and
-- list item delimiter.
-- RETURN: Number of list items on success, 0 if list is invalid.
-- NOTES:
-- MODIFICATION
-- HISTORY:
-- __________________________ __________ __________ __________ __________ ___
FUNCTION List_Item_Count(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ','
) RETURN NUMBER
IS
nItemCount NUMBER;
BEGIN
IF vList IS NULL
OR SUBSTR(vList,1,LENGTH(vLis tItemDelim iter)) = vListItemDelimiter
OR INSTR(vList||vListItemDeli miter,
vListItemDelimiter||vListI temDelimit er) != 0 THEN
RETURN 0;
END IF;
nItemCount := 0;
WHILE INSTR(vList||vListItemDeli miter,vLis tItemDelim iter,1,
nItemCount + 1) != 0 LOOP
nItemCount := nItemCount + 1;
END LOOP;
RETURN nItemCount;
END List_Item_Count;
-- __________________________ __________ __________ __________ __________ ___
-- | |
-- | GET LIST ITEM |
-- |_________________________ __________ __________ __________ __________ ___|
-- TITLE: Get List Item Function
-- TYPE: Public Packaged Fuction
-- VERSION: 1.0
-- CREATED: June 25, 1996
-- AUTHOR: Solomon Yakobson
-- GROUP:
-- Copyright(c)1996, Taconic Data Corporation
-- WARNING:
-- DESCRIPTION: This function will parse a given list of items delimited
-- by a given delimiter and return back a given list item.
-- List must have the following format:
--
-- <item_1><delimiter><item_2 ><delimite r>...<item _n>
--
-- This function does not validate list format. Use function
-- List_Item_Count to validate the list and get the number
-- of list items. Delimiter is a character string of any
-- length. If omitted, delimiter defaults to ','.
-- PARAMETERS: This function requires three input parameters: list, list
-- item delimiter and item number.
-- RETURN: Requested list item.
-- NOTES:
-- MODIFICATION
-- HISTORY:
-- __________________________ __________ __________ __________ __________ ___
FUNCTION Get_List_Item(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ',',
nItemNumber IN NUMBER
) RETURN VARCHAR2
IS
nItemPosition NUMBER;
nItemLength NUMBER;
BEGIN
nItemPosition := INSTR(vListItemDelimiter|| vList,
vListItemDelimiter,1,nItem Number);
nItemLength := INSTR(vList||vListItemDeli miter,vLis tItemDelim iter,
1,nItemNumber) - nItemPosition;
RETURN SUBSTR(vList,nItemPosition ,nItemLeng th);
END Get_List_Item;
END Parsing_Pkg;
/
SHOW ERRORS
Solomon Yakobson.
-- __________________________
-- | |
-- | PARSING_PKG |
-- |_________________________
-- FILE: PARSING.PKG
-- LOCATION:
-- TITLE: Parsing Package
-- TYPE: ORACLE PL/SQL Package
-- VERSION: 1.0
-- CREATED: June 25, 1996
-- AUTHOR: Solomon Yakobson
-- WARNING:
-- DESCRIPTION: This package will provide all components necessary to
-- perform various data parsing routines.
-- NOTES:
-- MODIFICATION
-- HISTORY:
-- __________________________
CREATE OR REPLACE PACKAGE Parsing_Pkg
AS
FUNCTION List_Item_Count(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ','
) RETURN NUMBER;
FUNCTION Get_List_Item(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ',',
nItemNumber IN NUMBER
) RETURN VARCHAR2;
END Parsing_Pkg;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY Parsing_Pkg
AS
-- __________________________
-- | |
-- | LIST ITEM COUNT |
-- |_________________________
-- TITLE: List Item Count Function
-- TYPE: Public Packaged Fuction
-- VERSION: 1.0
-- CREATED: June 25, 1996
-- AUTHOR: Solomon Yakobson
-- WARNING:
-- DESCRIPTION: This function will parse a given list of items delimited
-- by a given delimiter and return number of items in that
-- list. List must have the following format:
--
-- <item_1><delimiter><item_2
--
-- List can not be empty, can not start with list item
-- delimiter and can not have two list item delimiters next
-- to each other. Delimiter is a character string of any
-- length. If omitted, delimiter defaults to ','.
-- PARAMETERS: This function requires two input parameters: list and
-- list item delimiter.
-- RETURN: Number of list items on success, 0 if list is invalid.
-- NOTES:
-- MODIFICATION
-- HISTORY:
-- __________________________
FUNCTION List_Item_Count(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ','
) RETURN NUMBER
IS
nItemCount NUMBER;
BEGIN
IF vList IS NULL
OR SUBSTR(vList,1,LENGTH(vLis
OR INSTR(vList||vListItemDeli
vListItemDelimiter||vListI
RETURN 0;
END IF;
nItemCount := 0;
WHILE INSTR(vList||vListItemDeli
nItemCount + 1) != 0 LOOP
nItemCount := nItemCount + 1;
END LOOP;
RETURN nItemCount;
END List_Item_Count;
-- __________________________
-- | |
-- | GET LIST ITEM |
-- |_________________________
-- TITLE: Get List Item Function
-- TYPE: Public Packaged Fuction
-- VERSION: 1.0
-- CREATED: June 25, 1996
-- AUTHOR: Solomon Yakobson
-- GROUP:
-- Copyright(c)1996, Taconic Data Corporation
-- WARNING:
-- DESCRIPTION: This function will parse a given list of items delimited
-- by a given delimiter and return back a given list item.
-- List must have the following format:
--
-- <item_1><delimiter><item_2
--
-- This function does not validate list format. Use function
-- List_Item_Count to validate the list and get the number
-- of list items. Delimiter is a character string of any
-- length. If omitted, delimiter defaults to ','.
-- PARAMETERS: This function requires three input parameters: list, list
-- item delimiter and item number.
-- RETURN: Requested list item.
-- NOTES:
-- MODIFICATION
-- HISTORY:
-- __________________________
FUNCTION Get_List_Item(
vList IN VARCHAR2,
vListItemDelimiter IN VARCHAR2 DEFAULT ',',
nItemNumber IN NUMBER
) RETURN VARCHAR2
IS
nItemPosition NUMBER;
nItemLength NUMBER;
BEGIN
nItemPosition := INSTR(vListItemDelimiter||
vListItemDelimiter,1,nItem
nItemLength := INSTR(vList||vListItemDeli
1,nItemNumber) - nItemPosition;
RETURN SUBSTR(vList,nItemPosition
END Get_List_Item;
END Parsing_Pkg;
/
SHOW ERRORS
Solomon Yakobson.
ASKER
Hey syakobson,
I already had this in my mind. But I felt like there should be some better solution rather than this. Sorry, don't feel bad that I rejected your answer, I am looking for a still better option. Anyway, thanks for your help.
Regards,
Ramesh.
I already had this in my mind. But I felt like there should be some better solution rather than this. Sorry, don't feel bad that I rejected your answer, I am looking for a still better option. Anyway, thanks for your help.
Regards,
Ramesh.
hmm,
what about this
select a,
b,
Substr(y,1,Instr(y,'@')-1) c,
Substr(y,Instr(y,'@')+1,le ngth(y)) d from (
select a,
Substr(x,1,Instr(x,'@')-1) b,
Substr(x,Instr(x,'@')+1,le ngth(x)) y from (
select Substr('l0adad@15-apr-1999 @success@s elect count(*) from dual',1,Instr('l0adad@15-a pr-1999@su ccess@sele ct count(*) from dual','@')-1) a,
Substr('l0adad@15-apr-1999 @success@s elect count(*) from dual',Instr('l0adad@15-apr -1999@succ ess@select count(*) from dual','@')+1,length('l0ada d@15-apr-1 999@succes s@select count(*) from dual')) x
from dual))
meikl
what about this
select a,
b,
Substr(y,1,Instr(y,'@')-1)
Substr(y,Instr(y,'@')+1,le
select a,
Substr(x,1,Instr(x,'@')-1)
Substr(x,Instr(x,'@')+1,le
select Substr('l0adad@15-apr-1999
Substr('l0adad@15-apr-1999
from dual))
meikl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
a simple pl/sql script can solve this problem :
declare
qry_str varchar2(100) := 'l0adad@15-apr-1999@succes s@select count(*) from dual';
i number(2) := 1;
ctr number(2) := 0;
ctr1 number(2) := 1;
startchar number(2) := 0;
noofchars number(2) := 1;
begin
loop
ctr := instr(qry_str, '@', startchar + 1, 1);
if (ctr > 0) then
dbms_output.put_line (substr(qry_str, startchar + 1, (ctr - 1) - startchar));
else
dbms_output.put_line (substr(qry_str, startchar + 1, length(qry_str)));
exit;
end if;
startchar := ctr;
end loop;
end;
/
declare
qry_str varchar2(100) := 'l0adad@15-apr-1999@succes
i number(2) := 1;
ctr number(2) := 0;
ctr1 number(2) := 1;
startchar number(2) := 0;
noofchars number(2) := 1;
begin
loop
ctr := instr(qry_str, '@', startchar + 1, 1);
if (ctr > 0) then
dbms_output.put_line (substr(qry_str, startchar + 1, (ctr - 1) - startchar));
else
dbms_output.put_line (substr(qry_str, startchar + 1, length(qry_str)));
exit;
end if;
startchar := ctr;
end loop;
end;
/
ASKER
I not only need to parse the values but also need to store them in a variable.
ASKER
Hey syakobson,
I think yours is the one which suites my requirement. But still I need to know bit more about array declaration part before accepting yours as the answer. Could you explain me the declaration of array. That would be of great help.
Regards,
Ramesh.
I think yours is the one which suites my requirement. But still I need to know bit more about array declaration part before accepting yours as the answer. Could you explain me the declaration of array. That would be of great help.
Regards,
Ramesh.
ASKER
Hey syakobson,
I think yours is the one which suites my requirement. But still I need to know bit more about array declaration part before accepting yours as the answer. Could you explain me the declaration of array. That would be of great help.
Regards,
Ramesh.
I think yours is the one which suites my requirement. But still I need to know bit more about array declaration part before accepting yours as the answer. Could you explain me the declaration of array. That would be of great help.
Regards,
Ramesh.
well i think instead of using dbms_output, u can use an PL/SQL array or varray to store variables using the same script i sent....
ASKER
Thanks for the new idea of using arrays. That was really interesting and that solved my requirement also.
Do you need a PL/SQL proc to seperate the strings ?
What do you want to do with the seperated strings ?