Question 95479 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

How to extract digit from string in SQL? – Amazon web services

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search