CODEWITHSUNDEEP
sqloracle-database
SQL_Python_DataScience
SQL_Python_DataScience

Reputation: 79

How to cut from string some number in SQL Oracle

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?

Upvotes: 0

Views: 569

Answers (3)

Alex Poole
Alex Poole

Reputation: 191245

From your comments you want the original text to be split into multiple values, and then to have each of those stripped of leading zeros. You referred to multiple columns, which is OK but you have to know how many you could have - i.e. to know the maximum length the original string can be.

As an example where the maximum length is 24 you could split with:

select
  regexp_substr(text, '\d{8}', 1, 1) as chunk1,
  regexp_substr(text, '\d{8}', 1, 2) as chunk2,
  regexp_substr(text, '\d{8}', 1, 3) as chunk3
...

and then either just remove leading zeros to leave as strings:

select
  ltrim(regexp_substr(text, '\d{8}', 1, 1), '0') as str1,
  ltrim(regexp_substr(text, '\d{8}', 1, 2), '0') as str2,
  ltrim(regexp_substr(text, '\d{8}', 1, 3), '0') as str3

or convert to numbers:

select,
  to_number(regexp_substr(text, '\d{8}', 1, 1)) as num1,
  to_number(regexp_substr(text, '\d{8}', 1, 2)) as num2,
  to_number(regexp_substr(text, '\d{8}', 1, 3)) as num3

In each case, to handle a longer string you would need to add more column expressions, incrementing the occurrence number (and column name).

With your sample data put into a table t:

select text,
  to_number(regexp_substr(text, '\d{8}', 1, 1)) as num1,
  to_number(regexp_substr(text, '\d{8}', 1, 2)) as num2,
  to_number(regexp_substr(text, '\d{8}', 1, 3)) as num3
from t
TEXT NUM1 NUM2 NUM3
00001204 1204
00007645 7645
00012305 12305
00000078 78
0000120400000123 1204 123
000012340000012300040678 1234 123 40678
00000010 10
0000001000000100 10 100

If you wanted one row per value you could use a hierarchical query or recursive subquery factoring:

with rcte (text, chunk_num, chunk, remainder) as (
  select text, 1, substr(text, 1, 8), substr(text, 9)
  from t
  union all
  select text, chunk_num + 1, substr(remainder, 1, 8), substr(remainder, 9)
  from rcte
  where remainder is not null
)
select text, chunk_num, chunk, ltrim(chunk, '0') as str, to_number(chunk) as num
from rcte
order by text, chunk_num
TEXT CHUNK_NUM CHUNK STR NUM
00000010 1 00000010 10 10
0000001000000100 1 00000010 10 10
0000001000000100 2 00000100 100 100
00000078 1 00000078 78 78
00001204 1 00001204 1204 1204
0000120400000123 1 00001204 1204 1204
0000120400000123 2 00000123 123 123
000012340000012300040678 1 00001234 1234 1234
000012340000012300040678 2 00000123 123 123
000012340000012300040678 3 00040678 40678 40678
00007645 1 00007645 7645 7645
00012305 1 00012305 12305 12305

db<>fiddle

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Use LTRIM:

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

Or, use REGEXP_REPLACE:

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

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

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

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

Upvotes: 1

Related Questions