How to extract digit from string in SQL?-CodePudding

I have a string which has at least one digit per bracket. Now, I want to extract the digit(s). How do I do this in Redshift sql?

    ColumnA     ColumnB (output)
    (,,,3,)     3
    (2,,,)      2
    (,,,1)      1
    (1,,,3)     13

CodePudding user response：

You could use REGEXP_REPLACE. Here's a snippet:

CREATE TABLE x (col1 varchar(255))

INSERT INTO x VALUES ('(,,,3,)'),('(2,,,)'),('(,,,1)'),('(1,,,3)');
select col1, 
       regexp_replace(col1,'[^\d]','','g') as col2
from x;

col1	col2
(,,,3,)	3
(2,,,)	2
(,,,1)	1
(1,,,3)	13

Try it in SQLFiddle

CodePudding user response：

Jakob's answer would work. You can also do the same thing with REPLACE:

CREATE TABLE x (col1 varchar(255)) 
INSERT INTO x VALUES ('(,,,3,)'),('(2,,,)'),('(,,,1)'),('(1,,,3)')

SELECT REPLACE(
    REPLACE(
        REPLACE(
          col1, ',', ''
        ) ,')', ''
    ), '(', ''
) FROM x

replace
3
2
1
13

SQLFiddle