CODEWITHSUNDEEP
sqloracle-databaseplsql
Lawrencxe
Lawrencxe

Reputation: 81

Remove 2 characters in oracle sql

I have a column that contains 12 digits but user wants only to generate a 10 digits.

I tried the trim, ltrim function but nothing work. Below are the queries I tried.

ltrim('10', 'column_name')
ltrim('10', column_name)
ltrim(10, column_name)

For example I have a column that contains a 12 digit number

100000000123
100000000456
100000000789

and the expected result I want is

0000000123
0000000456
0000000789

Upvotes: 0

Views: 3651

Answers (2)

user5683823
user5683823

Reputation:

To extract the last 10 characters of an input string, regardless of how long the string is (so this will work if some inputs have 10 characters, some 12, and some 15 characters), you could use negative starting position in substr:

substr(column_name, -10)

For example:

with
  my_table(column_name) as (
    select '0123401234'    from dual union all
    select '0001112223334' from dual union all
    select '12345'         from dual union all
    select '012345012345'  from dual
  )
select column_name, substr(column_name, -10) as substr
from   my_table;

COLUMN_NAME   SUBSTR    
------------- ----------
0123401234    0123401234
0001112223334 1112223334
12345                   
012345012345  2345012345

Note in particular the third example. The input has only 5 digits, so obviously you can't get a 10 digit number from it. The result is NULL (undefined).

Note also that if you use something like substr(column_name, 3) you will get just '345' in that case; most likely not the desired result.

Upvotes: 4

Gianluca Musa
Gianluca Musa

Reputation: 805

try to use SUBSTR(column_name, 2)

Upvotes: -1

Related Questions