I have 1 record value = ABC2015000001.
How to select that record and show 000001 only?
That means I want select value 6 digits from behind.
3 Answers
Substring can be used to do this;
Oracle
select SUBSTR('ABC2015000001', length('ABC2015000001') - 5, 6) from DUAL
sql server
select SUBSTRING('ABC2015000001', len('ABC2015000001') - 5, 6)
select right('ABC2015000001', 6)
mysql
select SUBSTR('ABC2015000001', length('ABC2015000001') - 5, 6)
Comments
Praveen's syntax for Oracle works, but you can do it in a slightly simpler way, without the length() call or the second substring-length argument. substr() allows a negative position argument:
- If position is 0, then it is treated as 1.
- If position is positive, then Oracle Database counts from the beginning of char to find the first character.
- If position is negative, then Oracle counts backward from the end of char.
- If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.
Combining the last two points, you can pass -6 as the position to say you want to start 6 characters from the end, and omit the second argument so it gets the remainder of the string:
SQL> select substr('ABC2015000001', -6) from dual;
SUBSTR
------
000001
Comments
Which database type do you use?
Generally in almost all databases there is Substring(parameters) function.
Comments
DBMS.