Hi fellas,
Last week i got a task to write a sql statement to select a particular part of a string from given string. The part of the string i need to get separate is in the back end of the original string. Actually given string is path where valuable text document stored in the server. File name is included in this path from the back end. To process the file they need to read the full path from the database and get the file name separated from the original path string.
Actual string is like this,
Main_Folder\folder_1\Folder_2\textfile.txt
Every folder is separated by '\' sign and number of folders in string can be different by time to time.Apart from that, the length of the file name can be very. Therefore we cannot predict the length of the original file path and length of the file name as well.My task is to read the original string from back end and cut the string when first '\' sign meets.
To read the string from the back, I had to reverse the given string. We can use REVERSE key word for this. But normally 10g oracle forms doesn't support all the key words in sql. Therefore forms doesn't support for REVERSE as well. Forms doesn't recognize REVERSE as a key word.Therefore we have to use UTL package along with the reverse key word (only for forms).
Lets imagine the original string is like "Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt"
First of all I had to reverse the original string and then i can read it until I can find the first '\' sign. Relevant statement is,
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL;
Output will be "txt.eliFtxeT\redlof_buS\2_redloF\1_redlof\redloF_niaM"
Then I had to read the string till I can find the position of the first '\' sign. Relevant statement is,
SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))),'\')-1 FROM DUAL;
Output will be "12".
Therefore now I know that i have to substring the given string from 12th position to get only the file name separated. The relevant statement is,
SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL;
Output will be "txt.eliFtxeT".
Now I had to reverse the separated file name to get the original file name. For this process I had to use REVERSE statement again for the above mentioned output.
For the whole process, relevant statement is like below.
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW((SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL)))) FROM DUAL;
When we use this in the form, all the SELECT key words and FROM DUAL key words should be left.
Final output of the above mentioned statement will be,
"TextFile.txt".
I used INSTR to calculate the position of the first '\' sing in the reversed string and we must reduce 1 from that position number to get only the file name.
Last week i got a task to write a sql statement to select a particular part of a string from given string. The part of the string i need to get separate is in the back end of the original string. Actually given string is path where valuable text document stored in the server. File name is included in this path from the back end. To process the file they need to read the full path from the database and get the file name separated from the original path string.
Actual string is like this,
Main_Folder\folder_1\Folder_2\textfile.txt
Every folder is separated by '\' sign and number of folders in string can be different by time to time.Apart from that, the length of the file name can be very. Therefore we cannot predict the length of the original file path and length of the file name as well.My task is to read the original string from back end and cut the string when first '\' sign meets.
To read the string from the back, I had to reverse the given string. We can use REVERSE key word for this. But normally 10g oracle forms doesn't support all the key words in sql. Therefore forms doesn't support for REVERSE as well. Forms doesn't recognize REVERSE as a key word.Therefore we have to use UTL package along with the reverse key word (only for forms).
Lets imagine the original string is like "Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt"
First of all I had to reverse the original string and then i can read it until I can find the first '\' sign. Relevant statement is,
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL;
Output will be "txt.eliFtxeT\redlof_buS\2_redloF\1_redlof\redloF_niaM"
Then I had to read the string till I can find the position of the first '\' sign. Relevant statement is,
SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))),'\')-1 FROM DUAL;
Output will be "12".
Therefore now I know that i have to substring the given string from 12th position to get only the file name separated. The relevant statement is,
SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL;
Output will be "txt.eliFtxeT".
Now I had to reverse the separated file name to get the original file name. For this process I had to use REVERSE statement again for the above mentioned output.
For the whole process, relevant statement is like below.
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW((SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL)))) FROM DUAL;
When we use this in the form, all the SELECT key words and FROM DUAL key words should be left.
Final output of the above mentioned statement will be,
"TextFile.txt".
I used INSTR to calculate the position of the first '\' sing in the reversed string and we must reduce 1 from that position number to get only the file name.
