CODEWITHSUNDEEP
amazon-redshift
jaysunn
jaysunn

Reputation: 87

Strip remaining characters from field

I have the following query and I am trying to remove all characters after the first ( in a CREATE TABLE statement as well as the ( to show just the table name. I have a query that removes the ( and cleans up the table name, but I am still unable to get rid of the rest of the characters after the ( 

redshift_dev=# select stl.userid, trim(pg.usename) AS user,
regexp_replace(
(CASE UPPER(SPLIT_PART(text, ' ', 3))
WHEN 'IF'
THEN SPLIT_PART(text, ' ', 6)
ELSE SPLIT_PART(text, ' ', 3)
END), '(\\()','' ) AS table_name,
starttime as creation_date from stl_ddltext as stl join pg_user as pg on stl.userid = pg.usesysid where text ilike '%create table%' and pg.usename not like '%mcp_etl%' and pg.usename not like '%apiadmin%' and pg.usename not like '%afscloud%' order by creation_date desc limit 1;
 userid | user  |           table_name           |       creation_date
--------+-------+--------------------------------+----------------------------
    100 | admin | analytics.cluster_1000field1 | 2017-10-13 16:17:26.387716
(1 row)

Desired result:

 userid | user  |           table_name           |       creation_date
--------+-------+--------------------------------+----------------------------
    100 | admin | analytics.cluster_1000 | 2017-10-13 16:17:26.387716
(1 row)

Upvotes: 0

Views: 1464

Answers (1)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

I'm not sure what your create table stmts normally look like, but you can try using regexp_substr.

SELECT
  stl.userid
  , trim(pg.usename)        AS user
  , REPLACE(regexp_substr(text, '([\.a-z0-9A-Z]*)\\('), '(', '') as tablename
  , starttime               AS creation_date
FROM stl_ddltext AS stl
  JOIN pg_user AS pg
    ON stl.userid = pg.usesysid
WHERE text ILIKE '%create table%' AND pg.usename NOT LIKE '%mcp_etl%' AND pg.usename NOT LIKE '%apiadmin%' AND pg.usename NOT LIKE '%afscloud%'
ORDER BY creation_date DESC
LIMIT 1;

It basically tries to extract everything before the first ( and then replaces the (.

Upvotes: 2

Related Questions