Substr in Oracle SQL

Question

Both the below queries return the same result. Can you please let me know why and which is more appropriate?

select substr('87731000',0,8) from dual;

select substr('87731000',1,8) from dual;

Joel · Accepted Answer · 2016-05-06 17:10:13Z

4

This version is more appropriate:

select substr('87731000',1,8) from dual;

The second parameter is the start position. In oracle, the start position for a string is always 1.

Both statements work just fine, but that's only because the database is being helpful and assuming that if you enter '0', you mean to start at the beginning of the string.

From the oracle docs: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm

If position is 0, then it is treated as 1.

answered May 6, 2016 at 17:10

Joel

2,2671 gold badge20 silver badges24 bronze badges

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

3 Comments

Punter Vicky Over a year ago

Thanks a lot , Joel!

Mark Stewart Over a year ago

And if you use 0, then when someone else sees your code, and need to write a substr starting at character position 2, they may use 1 thinking it is zero-based!

OscarAkaElvis Over a year ago

@PunterVicky don't forget mark the question as resolved!

Collectives™ on Stack Overflow

Substr in Oracle SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related