CODEWITHSUNDEEP
sqloracle-database
indianbro
indianbro

Reputation: 29

How to extract a digit from number in oracle

please help with query how to extract digit '1' from below table using SQL in oracle.

Table
1000
1001
1010
0100

expected result ;

Table
1
11
11
1

Upvotes: 0

Views: 232

Answers (3)

MT0
MT0

Reputation: 167832

You can use the simple string function TRANSLATE (which is faster than regular expressions):

SELECT TRANSLATE(value, '10', '1') AS result
FROM   table_name

If you have more than binary digits then:

SELECT TRANSLATE(value, '1023456789.', '1') AS result
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '1000' FROM DUAL UNION ALL
SELECT '1001' FROM DUAL UNION ALL
SELECT '1010' FROM DUAL UNION ALL
SELECT '0100' FROM DUAL;

Both output:

RESULT
1
11
11
1

db<>fiddle here

Upvotes: 2

Gnqz
Gnqz

Reputation: 3382

You can use regexp_replace:

WITH dat AS
(
SELECT '1000' AS numb FROM dual UNION ALL
SELECT '1001' FROM dual UNION ALL
SELECT '1010' FROM dual UNION ALL
SELECT '0100' FROM dual
)
SELECT regexp_replace(numb,'[^1]','')
  FROM dat;

Upvotes: 0

VvdL
VvdL

Reputation: 3210

I expect you are giving us a simplified version of your problem? One way to achieve this is is using REGEXP_REPLACE to replace all characters but the character 1 with an empty space:

SELECT
   REGEXP_REPLACE(YOUR_COLUMN,'[^1]','') AS DESIRED_RESULT
FROM YOUR_TABLE

You can check out this example: db<>fiddle

Upvotes: 1

Related Questions