I have a column that is of varchar type, it contains dates and ‘#’: I am trying to convert the dates to the standard date format (YYYY-MM-DD) and leave the ‘#’ as it is whenever it occurs. Here is my code: The outcome column is also of varchar(10) type (same as the original column). I expected to get # whenever
Tag: sql
Output number of non-consecutive failures from historical data in Bigquery
this is related to my previous scenario. I have a dataset like this: Aside from outputting the timestamp in which a user first committed a failure, and consecutively commits a failure status every day, leading up to today (2022-04-29), I also want to output the non-consecutive block of days in which Karl or Andrea commits a failure. In this case,
Exclude blank column while Joining two SQL server tables
I have set of vehicle parts stored in two tables as per below: Source Table1: Vehicle_ID Part1 Part2 Part3 Part4 Part5 1 10 20 30 2 10 20 3 10 Source Table2: Vehicle_ID Part6 Part7 Part8 Part9 Part10 1 40 2 30 50 60 3 30 Required Table like below: Vehicle_ID Part1 Part2 Part3 Part4 Part5 1 10 20 30
How to query for delete the same data out of the table?
I want to delete it for the output to look like this. In one group there should be only 1 user_id. I get all user_id, group_id and count more than 1 but I don’t know how to delete duplicates and leave only 1 left. Ps. My English is probably not perfect, pls excuse any mistakes Answer Make a subquery to
MySQL count multiple GroupBy
I have data like this How can I get output like this: (with MySQL query) Answer Try this:
Azure SQL: Modify table data via UI instead of writing SQL query
Is there a way I can modify the table data in an Azure SQL database via some sort of UI (like Excel) instead of having to write SQL queries? Preferably I can modify it online in the Azure Portal instead of having to install some third-party software. Often times, I just want to edit a specific cell in the table
How to add plus(+) sign in every row of a column using SQL query?
I have a database with a column containing phone numbers. I want to add plus(+) sign in the beginning of every phone number. This is the query I’m using to insert any other character, but it doesn’t seem to work with + sign. It says 0 row(s) affected Rows matched: 4023 Changed: 0 Warnings: 0, which means nothing has changed.
partition by customer for distinct items
select customer_id, row_number()over(partition by customer id order by date) as rn from table How to get same rn when Item Id is the same? Below did not work: #1 select customer_id, row_number()over(partition by customer id, Item Id order by date) as rn from table Answer We can try to use DENSE_RANK instead of row_number window function If the optional PARTITION
How to access Package Public variable into SELECT Statement using EXECUTE IMMEDIATE?
/ / error ORA-06553: PLS-221: ‘G_VAR’ is not a procedure or is undefined Answer Obviously, you cannot call an internal variable of a package like that, presumably might convert to this one and call like this : or this : Edit : Indeed, not needed; you can alternatively call the statement by using EXECUTE IMMEDIATE as desired such as
How to delete more than one node with xml-query
with this code, I can remove one node (field_without_interest) of this xml element. But I would like to remove two nodes now( field_without_interest and PERSON_NUMBER). I have tried to place the for loop at differents places (You can see that in code. But I don’t think it useful to for to write every attemps in stackoverflow) but to no avail.