4

According to the ANSI SQL-1992 standard, page 132, this is the syntax for the ANSI substring function:

<character substring function> ::=
    SUBSTRING <left paren> <character value expression> FROM <start position>
    FOR <string length> ] <right paren>

A quick test in SQL developer on an oracle 10g schema:

SELECT SUBSTRING('ASDF' FROM 1 FOR 1) FROM DUAL;

Results in:

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 1 Column: 28

Does this mean Oracle 10g does not comply with this particular ANSI standard?

I simply would like to use an ANSI standard substring function instead of a vendor specific one.

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
JavaRocky
  • 19,203
  • 31
  • 89
  • 110

2 Answers2

6

oracle documentation for SUBSTR

but you can check this http://troels.arvin.dk/db/rdbms/#functions-SUBSTRING

Doesn't provide the standard SUBSTRING function. Provides SUBSTR(input,start-pos[,length]) instead (i.e. length is optional). Oracle provides a number of SUBSTR-variants (SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4, same syntax as for SUBSTR), mainly for handling various kinds of non-latin-only string-types. Oracle doesn't have support for string-extraction with the special SQL-style regular expressions. Instead, it has the REGEXP_SUBSTR function which offers string extraction, using POSIX-style regular expression pattern matching.

zudokod
  • 4,074
  • 2
  • 21
  • 24
  • 1
    Also take a look at [Oracle Compliance To Core SQL:2003](http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm#sthref10080). "Oracle has equivalent functionality for these subfeatures:", "E021-06, SUBSTRING function: use SUBSTR function instead". – Jon Heller Apr 17 '11 at 15:58
  • 1
    C'mon, how hard can it be for Oracle to add a SUBSTRING function? I see that it has a specific syntax (rather than being a plain function), but SQL is already full of this. – marcus Oct 27 '11 at 14:50
  • @marcus Probably just a minor modification in the lexer. – Lluis Martinez May 06 '16 at 12:45
-5

Try this

SELECT SUBSTRING('ASDF' ,1,1) FROM DUAL;

It will work in any standard

Ankit
  • 2,753
  • 1
  • 19
  • 26