Oracle PL-SQL “Regular expression” to replace each “(space)and(space)” with ‘,’ in a string

I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I’m using the powerful regular_expression for this but it does not give me the desired result.

select regexp_replace('x=y and g=h and u=1','(^[[:space:]]*)AND(^[[:space:]]*)', ',') from dual; 

I was wondering if you could help me out here. thanks in advance.

Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn’t end in that character

I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.

This is very small example, my actual data is huge with other columns also.

+------------+ |    key     | +------------+ | value_100  | | value_100P | | value_101  | | value_101  | | value_102  | | value_102P | | value_103P | | value_104P | +------------+ 

The query would output,

+------------+ |    key     | +------------+ | value_100P | | value_102P | +------------+ 

SQL Server 2019 Always On using Distributed Network Name

We’re running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we’re running SQL Server 2019. When we set up the cluster, it was set up to use a Distributed Network Name instead of a static cluster IP address. Thanks to this we shouldn’t need an internal load balancer according to these notes: https://github.com/MicrosoftDocs/azure-docs/issues/34648.

I’m struggling to understand exactly how this works though. Based on what I read, it seems like our connection strings will point to the DNS name of the cluster (let’s call it AgCluster). If I look in DNS, there is an A record for AgCluster pointing to sql1 and another pointing to sql2. When I use AgCluster in my connection string it seems to always connect me to the primary server, even if I have ApplicationIntent=ReadOnly set. When I query @@SERVERNAME I always get the same server.

So with the Distributed Network Name setup, what should I use in my connection strings to make sure read/write queries go to the primary and read only go to a secondary? Any guides on setting this up in general would be helpful. Thanks!

MariaDB Replication – Replicate only specific tables and views

Note: A backend developer here with little to no experience in setting up database servers and replication.

What needs to be done

Setup DB replication of an existing database with the following constraints:

  1. Replicate only a specific list of tables/views with different names in the replicated database.
  2. Change the name of the tables/views in the replicated database (during the replication process)
  3. Setup a user on the replicated DB with further restrictions with which only a set of table/view can be viewed/updated/deleted

Progress so far

I have already read the document here, however, I did not find anything concrete to help me move forward with all the use-cases I wish to support!

Use Case

Show only essential data to the external vendor.

PS: If there are any other approaches other than replication, would be happy to consider and implement that as well.

Select Into a temp table in DB2

Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2.

The root query looks roughly like this:

Select      Col1, Col2, Col3 From (     Select A.Col1, B.Col2, B.Col3     From SchemaA.TableA as A         Inner Join SchemaA.TableB as B             On A.Id = B.ParentId     Where          A.Col2 = 'TypeValue'     Fetch First 5 Rows Only -- This is not allowed in a union or sub select      Union      Select A.Col1, B.Col2, B.Col3     From SchemaB.TableA as A         Inner Join SchemaB.TableB as B             On A.Id = B.ParentId     Where          A.Col2 = 'TypeValue'     Fetch First 5 Rows Only -- This is not allowed in a union or sub select ) as Hybrid 

Because Fetch First X Rows is not allowed in subselects or with unions, this isn’t going to work. Since we’re limiting the output, it makes sense to limit the input.

I figure I can achieve a fake union by feeding my subselects into temp tables instead and returning that output instead.

In T-SQL, I can create a temp table on the fly with a query like this how would I do that in DB2?

Select * Into #Temp From TableA 

How would I do that in DB2? Or would do I need to create the table before inserting data into it?

Postgres- update a value in a array on particular index

I have a column named ids that is of type bigint[]. ids cane be null or of any length

It has values based on certain hierarchy.

My use case is I want to update value that is at a particular index. For example ids[2] = 50.

How can I achieve this?

I looked at this https://www.postgresql.org/docs/current/functions-array.html#ARRAY-FUNCTIONS-TABLE but couldn’t find anything that could help.

Thanks in advance.

Why is it important for every table to have a primary key?

So I have some tables in Sql Server that are essentially a list of sales, things like:

ProductID  SalesOrderID ProductFamilyID  ProductCost ProductSource 

and so on. In this case, none of the columns are necessarily unique, so I can’t create a primary key from any combination of them. In fact, the only constraint that I really have on the table is that I need every row in the table to be a unique combination of the columns. So I’m assuming something like a unique index would be the way to go there.

The only primary key I could add is something like an autoincrement primary key. But what would be the actual use of that, database wise? What are the possible problems with not creating a primary key for a table like this?

Why do tempdb spills still occur even with good row and data size estimates (better than actuals)?

We’re seeing tempdb DB spills for some hashing operations. If the estimates are indeed good as shown what would be the next thing(s) to look for? Looking for a generic answer without having to resort to the specifc query.

This is part of an SP. Just switched to 2019 version to see if it would auto adjust but still getting spills so far.

Microsoft SQL Server 2019 (RTM) – 15.0.2000.5 (X64)

Hash Match Warnings

Migrating from MariaDB to Mysql – Duplicate Constraint Name

I am attempting to migrate from MariaDB to MySQL by doing a mysqldump from MariaDB and then restoring it to MySQL (mysql:latest docker container).

I am getting the following error when importing into MySQL:

ERROR 3822 (HY000) at line 172: Duplicate check constraint name 'CONSTRAINT_1'. 

If I look at the mysqldump file I can see why this is happening. All boolean columns in my database have a constraints that looks something like:

CONSTRAINT `CONSTRAINT_1` CHECK (`bool_col_1` in (0,1)) CONSTRAINT `CONSTRAINT_2` CHECK (`bool_col_2` in (0,1)) CONSTRAINT `CONSTRAINT_3` CHECK (`bool_col_3` in (0,1)) 

These constraints were not explicitly created by me but implicitly by Flask-SQLAlchemy (I think).

Notice how the constraint names are incremented starting with CONSTRAINT_1. Well the problem is that each table starts incrementing its constraint names starting with CONSTRAINT_1. Thus the error I am seeing gets thrown when trying to create the second table. According to the MySQL docs, duplicate constraint names are not allowed. Apparently MariaDB allows them.

Is there a way to rename these constraints systematically or an alternative way to migrate the data?

Note: This fiddle tests duplicate constraint names and executes without error on MySQL. However if I run the same commands on a fresh MySQL container, it fails with the duplicate constraint.