CODEWITHSUNDEEP
databaseoracle-database
Novice
Novice

Reputation: 308

How to select some string in 1 column database table

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.

Upvotes: 0

Views: 36

Answers (3)

Alex Poole
Alex Poole

Reputation: 191245

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

Upvotes: 1

Praveen
Praveen

Reputation: 9335

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)

Upvotes: 1

Patryk Imosa
Patryk Imosa

Reputation: 794

Which database type do you use?

Generally in almost all databases there is Substring(parameters) function.

Upvotes: 0

Related Questions