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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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> 
Re: Substring and Instring in combination [message #674085 is a reply to message #674084] Thu, 03 January 2019 03:20 Go to previous message
ABG
Messages: 5
Registered: April 2018
Junior Member
Thanks a lot Michel and cookiemonster.
Previous Topic: TRIM function
Next Topic: filter based on extracting string
Goto Forum:
  


Current Time: Fri Jul 10 04:51:51 CDT 2020