I have a string like this
x=y and g=h and u=1 and I need to replace each
,. 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.
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 | +------------+
I need help with the following output. Tried case, Distinct, Group By, failed. Join might help, but still couldn’t write a query with join.
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!
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:
- Replicate only a specific list of tables/views with different names in the replicated database.
- Change the name of the tables/views in the replicated database (during the replication process)
- 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!
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.
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
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?
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 = 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.
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?
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)
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.