I have a SQL query which returns some rows having the below format: How can i filter to get rows which only have the format of ‘DB_host’ (place a condition to return values with only one occurrence of ‘_’) i tried using [0-9a-zA-Z_0-9a-zA-Z], but seems like its not right. Please sugges…
Tag: sql
How do I use one SQL statement to invalidate groups of records?
I’m working on an assignment for class. I need to write an SQL statement that returns every single country in the database where no single ethnic group makes up more than 25% of the population. This is the closest I can get: But, that only returns records where ethnic groups are over 25% population. It …
Use a variable or function in MYSQLs ALTER TABLE statement
In Mysql/MariaDB I like to set the comment of an existing column to a string containing the current date. But I only get SQL syntax errors. I’ve tried so far: Also not working: Of course this works: Is this possible without an external script? Answer demo
Minimum of Days Difference between Dates and Update PostgreSQL
I have a situation like, I need to find out which record is having minimum difference of days between atleast 2 dates. Like – Table Data is like Input Parameter : [aDate = 12-Nov-2020, Infold = 2] Result should be: Need to find the date difference between the input dates and existing dates, find the rec…
aggregating Posts with all their Hashtags using Postgres
I have something like Posts in FB/instagram. When a user searches for something, the input query is used to either find Posts that either have this query in their content, OR – one or more of post’s hashtags match this query. The problem : I can’t figure out how to have Posts returned with a…
How to combine two tables to get singel table in Hive
I have following tables and need to combine them in hive Could any one please help me how can we achieve this. I tried date part with coalesce and it is fine. But fam part is not able to merge into single column. Really appreciate your help. Thanks, Babu Answer You can use full outer join. However, union with…
Rewrite SQL query to remove duplicate SELECTs in [WHERE xxx IN] condition
I need to execute following query: What is bothering me – is that it has to select same values 3 times. I am sure that there is a better, proper way of doing it. Could you please suggest how this query might be rewritten? Answer You can use an EXISTS condition with an IN: Which is more or less the
Postgresql replace comma from right in position 3 and replace dot and comma left to that position
I am having a currency value with comma and dot. I need to change some European style currency to UK style value. I have tried using right() and replace functions but no luck. Could anyone please help me in writing replace function to handle this. Answer This is an issue in European countries, especially when…
Eliminate double quotes from a json field and deal with characters in PostgreSQL
Some time ago I needed help with a postgresql database with a jsonb field to convert numerical values surrounded by double quotes into standard numerical values and this solution worked very well: Eliminate double quotes from a json field in a selective manner in PostgreSQL I had a field named “test_jso…
SQL: What are phone number formatting options?
SQL: What are phone number formatting options? I query my SQL Database for a phone number: 816-123-4567 I want to output it is as: 816 123-4567. What steps do I take to achieve this result in an SQL environment? Answer A standard SQL solution would be: There may be simpler solutions in other databases. For in…