How to extract digit from string in SQL?

Question

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

For that 4th example, what's the intended value to extract? You've got two digits in the bracket. — Jakob Lovern
– Jakob Lovern, Commented Sep 6, 2022 at 22:21
@titutubs you say one digit per bracket, but your last row has two digits. — sleepystar96
– sleepystar96, Commented Sep 6, 2022 at 22:26
If you search and replace on [^\d]=>(empty) then it'll delete everything that's not a digit. See regex101.com/r/zIRBtS/1 — Jakob Lovern
– Jakob Lovern, Commented Sep 6, 2022 at 22:28

Jakob Lovern · Accepted Answer · 2022-09-06 22:57:28Z

2

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

edited Sep 6, 2022 at 22:57

answered Sep 6, 2022 at 22:39

Jakob Lovern

1,3518 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

titutubs Over a year ago

@Jajob Lovern what is the 'g' for? I am getting an error

Jakob Lovern Over a year ago

The 'g' is a regex flag indicating to replace all matches.

Jakob Lovern · Accepted Answer · 2022-09-06 23:02:40Z

2

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

edited Sep 6, 2022 at 23:02

Jakob Lovern

1,3518 silver badges28 bronze badges

answered Sep 6, 2022 at 22:47

sleepystar96

8567 silver badges15 bronze badges

1 Comment

Jakob Lovern Over a year ago

This answer is probably more portable tbh. I've had spotty reception with REGEXP_REPLACE across databases, while REPLACE is part of the ansi spec.

Collectives™ on Stack Overflow

How to extract digit from string in SQL?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related