Category "sql"

SQL Server - Regex pattern match only alphanumeric characters

I have an nvarchar(50) column myCol with values like these 16-digit, alphanumeric values, starting with '0': 0b00d60b8d6cfb19, 0b00d60b8d6cfb05, 0b00d60b8d57a2b

Removing leading zeros from a string in SQL Server

i have removed all leading Zeros in my column but what if the same column has a space its replaced by '0'.. Example: batch number has 000123, 0000145 but when t

[Python][SQL Machine Learning Services] Cannot install XGBoost on my SQL instance

I am trying to find a solution to my problem. We are trying to install XGBoost package on our MS SQL 2018 and we are facing this problem: Error Description The

Best way to get list of element combinations from two mysql tables

I have two tables: Table "products" ID Name Price Quantity 1 Product A 10 10 2 Product B 20 10 Table "promotions" ID Product ID Type Price Quantity 1 1 disco

Capitalize the first letter of each word without affecting the consecutive letters

I would like to make the first letter of each word capitalized. However, if the word next to the first letter is already capitalized, then it should not be affe

how to avoid long sql in clickhouse

As ClickHouse can run complex SQL efficiently, we now write hundred-lines-in-one SQL to get the real time analysis results(join lots of source table). And it br

Assign outcome from SQL query to column

I have a dataframe (test_df) that looks like this: dq_code dq_sql Results ID_24 select 'A' as B, 'B' as

Select foreign key matching multiple criteria in a joining table (SQLITE)

Preface: this is all in a fiddle here https://www.db-fiddle.com/f/pMLFrz59aeL7B2jUsmTt9X/1 I have a table that contains items (item #100, item #101...) that eac

Referencing JOIN columns within a subquery from an aggregate function

I'm trying to reference columns in JOIN tables of a subquery from an aggregate function, as follows: WITH my_activities AS (SELECT activities.*, owner.*

How to test a repository with a user that have update rights and the test needs tot add records to the database

I use mysql and spring 5, spring-data-jpa and spring-boot and JUnit5. This must be a common problem but I don't see te sollution. I have to test a repository wi

Vertica SQL: Disregard rows, based on two conditions

I need a where condition that considers to following for an entire table: If a 0 exists for an ID (in column d) then exclude everything that is >0, if 0 does

Best Method to Delete 12M rows from SQL table

I have a table currently with 100M+ rows of telemetry data. The table often receives duplicate records. I can identify duplicate records using a row_number() pa

AWS Athena/Presto SQL: Having trouble getting null values

I am doing a query in aws Athena where I want to get some total values, however I am having issues getting a column where the values are null, this column somet

How to get the last day of the month without LAST_DAY() or EOMONTH()?

I have a table t with: DATE LOCATION PRODUCT_ID AMOUNT 2021-10-29 1 123 10 2021-10-30 1 123 9 2021-10-31 1 123 8 2021-10-29 1 456 100 2021-10-30 1 456 90 2021-

How to share datafram from multiprocess to main process?

Here's the 2nd version coding I'm using now( it's from Booboo), it takes about 17mins to return query result, and data could be transfer to patrent process. fro

How can I concatenate all values in descending order that have the same primary key in HIVE?

I am using HIVE and I have a table like this: S.no ID applicant_num f_name l_name Primary Key 1 123 202201A1 akhil yadav 123~&~akhil~&~yadav 2 123 2022

How to get all the objects in a table where the coordinates intersects with polygon with SQL Spatial Data

I have an SQL table which called DATAB which contains thousands of objects ordered by the following colunms: [id] [angel] [latitude] [longitude] And i have this

Getting previous month of last year

I want to get the previous month data but of the following year. So if it's May 2022, I want the data for April 2021. Here is what I currently have, but it is s

Snowflake's `try_to_geography` raises an error instead of returning null

I am trying to run a query in Snowflake to convert a GeoJSON object into Snowflake's baked-in geospatial data types: SELECT id, -- some other columns TRY

Getting previous month of last year

I want to get the previous month data but of the following year. So if it's May 2022, I want the data for April 2021. Here is what I currently have, but it is s