How to cut from string some number in SQL Oracle-CodePudding

I have a problem witch cut some text (number) in oracle

I must take only number withn't first '0' before first number'1-9' but in this what I must take are also '0' that I need '00001204'

example '00007645', '00012305', '00000078' and '0000120400000123' or '000012340000012300040678'

len from sequence number is always this same 8 or 16 or 24 etc and from each 8 I must remove first seqence with '0' and take separate number

could some one help me?

CodePudding user response：

Use LTRIM:

SELECT LTRIM(seq_num, '0')
FROM yourTable;

Or, use REGEXP_REPLACE:

SELECT REGEXP_REPLACE(seq_num, '^0 ', '')
FROM yourTable;

CodePudding user response：

You can use REGEXP_REPLACE() function with '[^1-9]' pattern such as

SELECT REGEXP_REPLACE(col,'[^1-9]')
  FROM t -- your table