How to get the result ‘xxx’ from blanket [ ] in presto. I can’t find the proper regexp to fix my problem Could you help me out? Thank you Answer If you have strings like this: and want to extract substring between square brackets, use this:
Tag: sql
How to view hourly results during the time specified in Postgres
I want to create a query that returns a list of timezones as a result when the following conditions are specified. How can I get this? Answer Use generate_series to create the records from the time interval, and jsonb_build_objebt with jsonb_agg to create your json document: Demo: db<>fiddle
sqlite3 INSERT INTO UNION SELECT
Table columns: pk is the auto-incrementing primary key An error occurs when I execute this SQL DB Error: 1 “table table1 has 4 columns but 3 values were supplied” Answer You should explicitly specify which columns in table1 your insert is targeting: When you omit the select list entirely, SQLite w…
Convert table data and reformat
I have table named Table id Level1 Level2 Level3 1 US CA 13000 2 FR PA 30000 3 US CA 24000 4 US LA 10000 5 UK LN 500 6 UK LN 600 7 FR PA 888 8 FR DF 1000 and I would like to convert it to the (First Conversion) below format which will sum Level2 and column
Rails – Relation passed to #or must be structurally compatible. Incompatible values: [:joins]
I’d like to merge two queries in my rails app. Each one is not very complex but I can’t merge them. user.rb package.rb I’m running into this error: Relation passed to #or must be structurally compatible. Incompatible values: [:joins] Answer I think what you’re looking for is a subselec…
Display records that are not present in a table and a given date range
Let’s say I have two tables – Student and Student Attendance tables. The Student table displays basic information about the student: Student# FirstName LastName 201710 John Smith 201711 John Doe 201712 Anna Sy 201713 Chris Dy While the Student Attendance table displays the TimeIn and TimeOut of th…
designing a follower/following schema between two tables in SQL?
suppose we have two tables: Brands Users i want to design a system where: Brands can follow Brands Brands can follow Users Users can follow Brands Users can follow Users this is what i have done so far: ed_id: user_id of the followed er_id: user_id of the follower note: the two tables are different but i have…
How to extract numbers from a string SQL
Ts there a way to extract numbers from a string? In my database there is a column called Reference and it contains multiple numbers CM|319|14163|||109|405000 Is there a way to get the first number like this? select CM|%s|… as ParentId from table So the output should be 319 And maybe even multiple like s…
Trigger to create or update records in another table
Edit: This question is mostly solved. What I am still trying to do might require a different approach. I am adding a trigger to a legacy database table, in order to automatically create new records for a related table. This is not homework. Here is my DB Fiddle on SQL Server 2017. https://dbfiddle.uk/?rdbms=s…
How do I show all records when one field is null in SQL?
I have my code but the @NAME is an optional field for searching. If the @NAME is blank or NULL, it should show all records. How do I approach this? Thanks! Answer There are some ways to do it the easy way. I would do something like this. You can use the Function ISNULL to handle blank or NULL. With