Detecting multiple REGEX outputs

wizkids121 0
Viewed 31

I have a table that looks like this and I am using Redshift SQL:

   CREATE TABLE mytable(
   userid       INTEGER  NOT NULL PRIMARY KEY,
   username     VARCHAR(9) NOT NULL,
   display_name VARCHAR(14) NOT NULL,
   first_name   VARCHAR(7) NOT NULL,
   last_name    VARCHAR(6) NOT NULL,
   bio          VARCHAR(32) NOT NULL
);
INSERT INTO mytable(userid,username,display_name,first_name,last_name,bio) VALUES (1234,'snoozebed','Michael Thomas','Michael','Thomas','Living in Maryland');
INSERT INTO mytable(userid,username,display_name,first_name,last_name,bio) VALUES (5678,'jeffdells','Jeff Dells','Jeff','Dells','Working in California, from Ohio');

I am trying to create a new column that extracts the name of a state from the bio column in the original.

I have the following query:

SELECT
*,
REGEXP_SUBSTR(bio, 'Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming') AS full_state
FROM mytable

That works fine for the first row in this example, but the problem arises when it comes to an example like the second row, as it returns a table that looks as such:

enter image description here

But I want it to look like this:

enter image description here

And then once that is complete, I want to then split the values to be their own row with all the other columns remaining the same, as such:

enter image description here

What would be the most effective way to do this?

amazon-redshift sql
1 Answers

You are close. There is an "occurrence" optional argument to regexp_substr() that will give you a later match. You can just pull all the different occurrences by cross joining with a few numbers (look at the end of the regexp_substr() below). Like this:

with recursive numbers(n) as (
select 1 as n
union all
select n + 1
from numbers
where n < 10
)
SELECT
*,
REGEXP_SUBSTR(bio, 'Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming',1,n,'i') AS full_state
FROM mytable
cross join numbers
where full_state <> '';
Bill Weiner0
Related