Trying to return only part of a string in SQL

Question

I currently have the following query:

SELECT SPEAKERNAME, 
       SUBSTR(SPEAKERADDRESS, CHARINDEX('-', SPEAKERADDRESS), 4)
  FROM SPEAKER;

I am tyrying to only return the postcode from SPEAKERADDRESS and the postcode is always after '-' but i am getting an invalid identifier (ORA-00904) error for CHARINDEX

Oracle doesn't have CHARINDEX, use INSTR instead: sqlines.com/oracle/functions/instr — Jacky
– Jacky, Commented Oct 5, 2017 at 10:09
2113 and the addresses are all of different lengths but this is always 4 digits — Timothy Bolton
– Timothy Bolton, Commented Oct 5, 2017 at 10:13

Mr. Bhosale · Accepted Answer · 2017-10-05 10:41:47Z

1

You can use SUBSTR and INSTR:

SELECT SPEAKERNAME, 
       SUBSTR(SPEAKERADDRESS, instr(SPEAKERADDRESS, '-')+1 ,4)
  FROM SPEAKER;

Check Demo.

answered Oct 5, 2017 at 10:41

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to return only part of a string in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related