Home » SQL & PL/SQL » SQL & PL/SQL » Substring and Instring in combination (11G)
Substring and Instring in combination [message #674071] |
Wed, 02 January 2019 00:44 |
|
ABG
Messages: 5 Registered: April 2018
|
Junior Member |
|
|
[/img]Hi All, I have basic understanding of substring and instring functions, but below code which combines these functions together ,I am having confused
for the last '-1' and '+1' ,I have attached the image,could some one please help.
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;
[Updated on: Wed, 02 January 2019 00:55] Report message to a moderator
|
|
|
Re: Substring and Instring in combination [message #674073 is a reply to message #674071] |
Wed, 02 January 2019 01:44 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The "+1" is because you want the string that starts at the character following the first comma.
The "-1" is because you don't want the second comma in the final string; just a matter to count the characters between the 2 commas.
I recommend you to check removing one and/or the other one to see what happens.
[Edit: typo]
[Updated on: Thu, 03 January 2019 05:19] Report message to a moderator
|
|
|
Re: Substring and Instring in combination [message #674084 is a reply to message #674073] |
Thu, 03 January 2019 03:16 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
When working out the effect of combined functions it often helps to split them out to see what each is doing individually, which is what Michel was saying in his last sentence:
SQL> SELECT 'abc,def,ghi' AS string,
2 SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
3 INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1) AS combo,
4 INSTR('abc,def,ghi',',', 1, 1)+1 AS first_instr,
5 INSTR('abc,def,ghi',',',1,2) AS second_instr,
6 INSTR('abc,def,ghi',',',1,1) AS third_instr,
7 INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1 AS last_substr_parameter
8 FROM dual;
STRING COMBO FIRST_INSTR SECOND_INSTR THIRD_INSTR LAST_SUBSTR_PARAMETER
----------- ----- ----------- ------------ ----------- ---------------------
abc,def,ghi def 5 8 4 3
SQL>
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 12:55:02 CDT 2024
|