All Questions
Tagged with amazon-redshift regex
145 questions
9
votes
1
answer
10k
views
Redshift / Regular Expression (Negative Lookahead) does not work
When I use negative lookahead (something like (?!abc)) on redshift,
redshift returns the error like this:
-- Find records that do not start with abc
select * from table_a where column_a ~ '^(?!abc).+$...
- 1,163
8
votes
5
answers
7k
views
How to use a regex capture group in redshift (or alternative)
I have a field in a redshift column that looks like the following:
abcd1234df-TEXT_I-WANT
the characters and numbers in the first 10 digits can be either letters or numbers.
If I use a capture ...
- 313
7
votes
3
answers
3k
views
How to do dynamic regex matching, in redshift?
So, I have a table with one of columns(say A) a "string", and another column having the corresponding "regex pattern", is it possible to check if the regex matches string value in column A for every ...
- 185
6
votes
4
answers
22k
views
Redshift SQL - Extract numbers from string
In Amazon Redshift tables, I have a string column from which I need to extract numbers only out. For this currently I use
translate(stringfield, '0123456789'||stringfield, '0123456789')
I was ...
- 835
5
votes
3
answers
14k
views
How can I find any non ASCII characters in Redshift database
I've a database table I'd like to return all values where a column contains a non ASCII character anywhere in the string.
Is there an easy way to do this?
I've tried this
select col_name, ...
- 335
5
votes
2
answers
5k
views
Use Regex from a column in Redshift
I have 2 tables in Redshift, one of them has a column containing Regex strings. And I want to join them like so:
select *
from one o
join two t
on o.value ~ t.regex
But this query throws an error:
[...
- 2,375
4
votes
2
answers
3k
views
SQL Regex to select string between second and third forward slash
I am using Postgres/Redshift to query a table of URLs and am trying to use
SELECT regex_substr to select a string that is between the second and third forward slash in the column.
For example I need ...
- 951
4
votes
1
answer
4k
views
Redshift regular expression for domain extraction
I'm trying to form a regular expression for REGEXP_SUBSTR (Redshift) which will extract the sub-domain & domain part from any given URL.
I tried many suggestions from stackoverflow: regular-...
- 41
4
votes
4
answers
112
views
Regexp : any chr but ignore word
Trying to capture server names from string.
A server name can be
letters + digits
letters + digits + letters (but not 'root')
Problem is that in circumstances the word 'root' gets added to the end ...
- 1,055
3
votes
3
answers
2k
views
Regex to remove "-" from the middle of a timestamp string in postgresql
I have a table that has the column hour that is not in the right timestamp format:
hour
---------------------
2014-12-06-01:44:35
2014-12-06-01:44:35
2014-12-06-01:44:35
2014-12-...
- 35k
3
votes
4
answers
10k
views
Redshift Extract String Between Two Patterns (regexp_substr)
I'm trying to extract the text following deviceSerialNumber= that occurs until either a & or the end of the string. Data looks like
someddata&=somedataagain&deviceSerialNumber=...
- 11.6k
3
votes
1
answer
1k
views
Regex to convert CamelCase to snake case in Redshift
I am trying to convert CamelCase to either snake case or separated by a delimiter using regex in SQL (AWS Redshift). So something like
regexp_replace(MyString, '([A-Z]+)', '-$1')
except I need to ...
- 793
3
votes
3
answers
9k
views
Extracting Values from Array in Redshift SQL
I have some arrays stored in Redshift table "transactions" in the following format:
id, total, breakdown
1, 100, [50,50]
2, 200, [150,50]
3, 125, [15, 110]
...
n, 10000, [100,900]
Since this format ...
- 65
3
votes
1
answer
4k
views
redshift sql using regular expression to get first, second, third values from a string which is like following
pattern: d0=123;d1=134;d2=123;d3=13;d4=33;d5=44;d6=55;
If want to get the first number between = & ;
output:
123
If I want to get the second occurrence between = & ;
output:
134
regex ...
- 73
2
votes
1
answer
17k
views
REGEXP_REPLACE Punctuation in Redshift
I'm trying to use REGEXP_REPLACE to remove all punctuation from a varchar. I'm using the following:
regexp_replace(d.NAME, [.,\/#!$%\^&\*;:{}=\-_`~()])
But it gives me an error, saying:
...
- 523