Skip to content

Tag: vertica

REGEXP_SUBSTR to extract a substring from Vertical SQL

I am trying to run the below query to get a substring from a string but it gives wrong result. I am not sure how to use the regex since the regex I am using works well with PCRE. I am expecting to get ndehqb-a3g4-a10 in this case but it prints the original string itself. The ask is to get

SQL: Get row number which increases every time a value changes

I have the following table in Vertica: The table is ordered by column_1 and column_3. I would like to add a row number, which increases every time when column_1 or column_3 change their value. It would look something like this: I tried using partition over but I can’t find the right syntax. Answer Vertica has the CONDITIONAL_CHANGE_EVENT() analytic functions. It

Convert day of week name to day of week number Vertica

With this way – SELECT TO_CHAR(NOW(), ‘DAY’); I can get a day of week name(E.G. MONDAY). With SELECT dayofweek(NOW()); I can get the number of the day of week. (E.G 1) QUESTION: How to convert day of week name to day of week number. (E.G MONDAY -> 1) SMTH like TO_NUMBER(‘MONDAY’, ‘DAY’); –> 1 (just for example, don’t working) Answer

Case statements around values in a list

I have a table that looks like below after performing certain joins: create table test(id varchar(10), sub varchar(20)); insert into test values(1,’a’) insert into test values(1,’b’) insert into …