Given a date column with a value 2020-05-01, I want to return 2020-Q2. The QUARTER() function is not available due to the Hive version we are using. I can get the quarter number with: (INT((MONTH(yyyy_mm_dd)-1)/3)+1). When I try to combine this with the YEAR() function and strings, I get null: How can I properly concatenate this to get the desired
Tag: string
Date conversion in SQL from 10-09-2020 to 2020-09-10
I want to convert it for comparing data & here I am stuck. I have data in date column like 10-09-2020 where convert(varchar, date, 111) >= ‘2020-09-10’ AND convert(varchar, date, 111) <= '...
How to count non-zero numbers in a comma separated string in oracle sql
Want to count non-zero numbers from a comma separated string in oracle 12.10. If my column has data as 2000,300,0,0 then count it as 2. This gives me 2000 and 300 as result but what’s next to count it. select regexp_substr(‘2000,300,0,0′,'[^,]+’,1,level) from dual connect BY to_number(regexp_substr(‘2000,300,0,0’, ‘[^,]+’,1,level)) > 0 Thanks for helping me. I know I am missing a basic
postgres – aggregate items in text array
I have an array of text items, each text item in the array is a delimited string, here is a sample: drop table if exists tmp; create table tmp(x text); insert into tmp select (‘1~41.5~50~1|2~43.72~…
Group rows by the same value in the field, while matching on partial value only
I have a table that has many rows (between a few 1000s to a few million). I need my query to do the following: group results by the same part of the value in the field; order by the biggest group first. The table has mostly values that have only some part are similar (and i.e. suffix would be different).
How to transform data into a map using group by in Hive SQL?
I have data like below …and I want to create a map with lecture as the key and count as a value. How can I get an output like below? Answer If you can live with count being a string, you probably be able to use Hive str_to_map() function to get a desired map. That will require a couple of
How to sort numbers last in PostgreSQL?
Found many related answers, but nothing that did this. How to sort by numbers last: And, preferably (but not necessary) PostgreSQL 11.9 Also, probably don’t want to use a regex for performance reasons. Don’t want to create an index either… Answer I think a regex is the right way to do this: This puts first rows that contain no digit
Split value into multiple rows
The following are two tables I have in the database mavenmovies. testLocation (TABLE 1) CREATE TABLE mavenmovies.testLocation ( id INT AUTO_INCREMENT PRIMARY KEY, State varchar(255), name …
Mysql group by included the whitespace as same value
I have this table and this value I need to group by the data and count how many data that have same value, so i used group by and count the result not what i expect because the data for ID 1,2,5, and 3 is counted as same value like below result how could i make the result like expected
Oracle SQL select characters after numeric portion of a string
I have the string “Serenity Lane – Barbur Boulevard 10920 SW Barbur Blvd Portland, OR 97219” and I want to select the first 13 characters from it :10920 SW Barb Is there a way to select only the first 13 characters after the first numeric character? In this example, the first thirteen characters starting at 1. Answer You can use