How to extract a digit from number in oracle-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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;