Looks like translate works for multi translations of single characters (abc -> xyz), but Replace in snowflake will only do 1 replacement. I have a list of JSON objects in a Snowflake SQL database where I need to replace 60 static items into 60 something else items. I was wondering if Replace (or another function) could leverage a dictionary table for this... But I'm guessing the simplest I can do is a For-Loop using a dictionary table. Like "for each X in dictionary table, replace (dict.A->dict.B) in fact table." Or is there a simpler method that doesn't require a for-loop?

I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i want to be able to replace all the words apple and orange that exists in table 2 and replace them with ##, is there a query that does this ? my actual table 1 has about 16k words and table 2 has 8 million words. most of the examples on google suggests i type the words to find but that is not possible when i have 16k words I am using mysql workbench and an absolute beginner (from comment) table 1 has 16k names in a column - table 2 has emails and text in columns - i want to be able to censor out all the names in table 2 using the names in table one as a reference

I have multiple MySQL databases(50+) and I want to replace a string in all of them, is there anyway to search and replace in all the databases? Server is CentOS and I have SSH root access.

I have two tables with identical structures: Table1 and Table2 id INT PRIMARY KEY, a TINYINT, b TINYINT, c TINYINT Table1 is actively being used by queries from other sessions; Table2 is not referenced/accessed by anything else. My goal is to end up with just Table1, but having the data from Table2. (Note that Table2 and Table1 do not have the same set of id-PRIMARY KEY.) What is the best way to do this without risking Table1 not existing momentarily for other sessions? I know I can do something like: DROP TABLE Table1; ALTER TABLE Table2 RENAME Table1; But am not sure how to ensure nothing tries to query Table1 while it doesn't exist or is empty. Do I use transactions or lock the tables or something else? (Ideally, I'd want any other sessions' queries to return either old Table1 or new Table1 data rather than failing.) I am using CodeIgniter in PHP to make the calls. **UPDATE:** Looks like this post answers it using a method from the MySQL RENAME TABLE Documentation: RENAME TABLE Table1 TO Table1_old, Table2 To Table1; And then I can DROP Table1_old at my leisure

I take a Derived Column Transformation to replace a value as soon as a condition on two other columns is met. How can I see in the data flow how often a value changes by this replacement? Is there a way to show it on the output arrow, or how else can I show the changes inside the data flow? #### PS (not needed for the Q/A) I want to check whether the string input is the same as the lookup output. But sometimes, names (first names and last names) have more than one name in the value, like Taylor-Smith against Smith, or Bob Thomas against Bob. People might marry or might in times fill their two first names so that data might not match over two master data accounts.

(DT_BOOL)(FINDSTRING(name,[Lookup.name],1)) ? 1 : [score_name]

The value of column score_name is replaced by 1 as soon as the name is found inside the Lookup.name, else the value is kept which is between 0 and 1. "Name in Lookup?": You could take any other example like [Replace column values?](https://dba.stackexchange.com/questions/47980/replace-column-values) , the question does not depend on what is done.

I am trying to solve The Blunder challenge under MySQL database and I am running into an error in what I thought to be a correct answer: `SELECT CEIL(AVG(Salary) - AVG(CAST(REPLACE(CAST(Salary AS VARCHAR(255)), '0', '') AS DECIMAL(14,2)))) AS avg_error FROM Employees;` In plain words, I tried converting the Salary (which is originally of a number type) into a varchar, remove any 0s appearing in it, convert it back to a number. I then calculated the average of the error and took a ceiling of it. However, when I run the code, I get the following error message: `Compiler Message Runtime Error Error (stderr) ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(255)), '0', '') AS DECIMAL(14,2)))) AS avg_error FROM Employees' at line 1 Your Output (stdout) ~ no response on stdout ~` I don't know where I made a mistake. Could you tell me how I can fix this, and if there is another way of a solution?

MySQL: 8.0.35 - Source distribution I have longtext (DC2Type:array) column and incorrect some characters values in a column string, so i want to replace this data format: a:2:{s:7:"content";s:6:"text 1";s:3:"url";s:19:"https://someurl.com ";} to this data format: a:2:{i:0;s:6:"text 1";i:1;s:19:"https://someurl.com ";} so replace: s:7:"content" to: i:0 and replace: s:3:"url" to: i:1 how to do it ? regards

I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced. I am new to Postgres. My function ufn_cie_easy() performs way too slow: DECLARE letter char = ''; str_result TEXT = ''; x integer; y integer; asc_code int; BEGIN y:=1; x:=char_length(arg); LOOP letter=substring(arg from y for 1); asc_code=ascii(letter); IF (asc_code BETWEEN 47 and 58) or (asc_code BETWEEN 65 and 90) or ( asc_code BETWEEN 97 and 122) THEN str_result := str_result || letter; ELSIF (asc_code BETWEEN 192 and 197) THEN str_result := str_result || 'A'; ELSIF (asc_code BETWEEN 200 and 203) THEN str_result := str_result || 'E'; ELSIF (asc_code BETWEEN 204 and 207) THEN str_result := str_result || 'I'; ELSIF (asc_code BETWEEN 210 and 214) OR (asc_code=216) THEN str_result := str_result || 'O'; ELSIF (asc_code BETWEEN 217 and 220) THEN str_result := str_result || 'U'; ELSIF (asc_code BETWEEN 224 and 229) THEN str_result := str_result || 'a'; ELSIF (asc_code BETWEEN 232 and 235) THEN str_result := str_result || 'e'; ELSIF (asc_code BETWEEN 236 and 239) THEN str_result := str_result || 'i'; ELSIF (asc_code BETWEEN 242 and 246) OR (asc_code=248) THEN str_result := str_result || 'o'; ELSIF (asc_code BETWEEN 249 and 252) THEN str_result := str_result || 'u'; ELSE CASE asc_code WHEN 352 THEN str_result := str_result || 'S'; WHEN 338 THEN str_result := str_result || 'OE'; WHEN 381 THEN str_result := str_result || 'Z'; WHEN 353 THEN str_result := str_result || 's'; WHEN 339 THEN str_result := str_result || 'oe'; WHEN 382 THEN str_result := str_result || 'z'; WHEN 162 THEN str_result := str_result || 'c'; WHEN 198 THEN str_result := str_result || 'AE'; WHEN 199 THEN str_result := str_result || 'C'; WHEN 208 THEN str_result := str_result || 'D'; WHEN 209 THEN str_result := str_result || 'N'; WHEN 223 THEN str_result := str_result || 'ss'; WHEN 230 THEN str_result := str_result || 'ae'; WHEN 231 THEN str_result := str_result || 'c'; WHEN 241 THEN str_result := str_result || 'n'; WHEN 376 THEN str_result := str_result || 'Y'; WHEN 221 THEN str_result := str_result || 'Y'; WHEN 253 THEN str_result := str_result || 'y'; WHEN 255 THEN str_result := str_result || 'y'; ELSE str_result := str_result; END CASE; END IF; y:=y+1; exit when y=x+1; END LOOP; return str_result; END;

I am trying to write a regex that can replace '[', ']' and '].' with '_' and in cases where ']' is the last character it should be replaced with '' but I am struggling to come up with a regex for it that works in all cases because Snowflake does not support lookahead & lookbehind. My question is, has anyone tried/achieved to do this before? or is it impossible and I should just give up?? Eg: - look_behind --> look_behind_0 - look_behind_positive --> look_behind_0_positive_1

I have a column which contains string values of the format e.g AB12345678 - two letters followed by 8 numeric digits. I need to write a MySQL query to insert a 5 before each set of 4 digits thus AB12345678 becomes AB5123455678. I'm close, the following works for one value in the column, but not for multiple values - all values are written with the same first value encountered it seems.

SELECT @twoeight := os_grid_ref FROM projects WHERE os_grid_ref REGEXP '[A-Z]{2}[0-9]{4}[0-9]{4}';
SELECT @prefix1 := SUBSTR(@twoeight,1,2);
SELECT @part1 := SUBSTR(@twoeight,3,4);
SELECT @part2 := SUBSTR(@twoeight,7,4);
UPDATE projects SET os_grid_ref=CONCAT(@prefix1,'5',@part1,'5',@part2) WHERE os_grid_ref REGEXP '[A-Z]{2}[0-9]{4}[0-9]{4}';

What do I need to adjust so that the query can modify each value in the column individually. Thank you.

![enter image description here ][1] in the school database course they teaches us with oracle database & i have a macbook what is the best replacement database to use?

Analyzing a simple query, I noticed that the REPLACE function increases the estimated row size. Look at the following query, executed on AdventureWorks: select p.BusinessEntityID, REPLACE(p.FirstName, 'a', 'b') as X from Person.Person p The following is the execution plan. The estimated row size, starting from 65 B, goes up to 4015 B only applying the Compute Scalar component that is related to the REPLACE function. Can anyone give an explanation about it? The test has been done on Sql Server 2022. Thanks in advance.

I need to write a store procedure to update link reference in my DB. Link can be contained in few nvarchar field that contains JSONs (that could contains some urls). To do so i update the tables in batches of 8129 items per iteration, so that the machine will not hang (in theory). But now the code seems to hang anyway, it will not print any message and the procedure keep running (without affecting any data) for many minutes, untill i have to kill the procedure (that in meanwhile seems to haven't affect any data). If i try to use the same logic on a toy example, i get no issue, so i think my problem is due the fact the table is big (few hundred thousand of rows). Here the minimal example that is working, the exact same code on bigger table hang apparently doing nothing (tested with SQL Server 2019). Procedure code : ALTER PROCEDURE [dbo].[SiteUrlChangeURL] @FullOldUrl nvarchar(500), @FullNewUrl nvarchar(500) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @FullOldUrl = ISNULL(@FullOldUrl,''); SET @FullNewUrl = ISNULL(@FullNewUrl,''); IF ( LEN(@FullOldUrl) <= 0 OR LEN(@FullNewUrl) <= 0 ) BEGIN PRINT('Invalid parameters'); RETURN 1; END --ARTICLE RAISERROR ('updating articles',0,1) WITH NOWAIT; WHILE 1=1 BEGIN UPDATE TOP (8196) [dbo].[tbl_ana_Articles] SET [_ATTRIBUTES] = REPLACE([_ATTRIBUTES] , @FullOldUrl, @FullNewUrl) ,[_DOCUMENTS] = REPLACE([_DOCUMENTS] , @FullOldUrl, @FullNewUrl) ,[_SEO] = REPLACE([_SEO] , @FullOldUrl, @FullNewUrl) ,[_TRANSLATIONS] = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl) ,[_TAGS] = REPLACE([_TAGS] , @FullOldUrl, @FullNewUrl) ,[_NOTES] = REPLACE([_NOTES] , @FullOldUrl, @FullNewUrl) WHERE [_ATTRIBUTES] like '%' + @FullOldUrl + '%' OR [_DOCUMENTS] like '%' + @FullOldUrl + '%' OR [_SEO] like '%' + @FullOldUrl + '%' OR [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR [_TAGS] like '%' + @FullOldUrl + '%' OR [_NOTES] like '%' + @FullOldUrl + '%' IF (@@ROWCOUNT <= 0) BEGIN BREAK; END END RETURN 0; Example : CREATE TABLE [dbo].[tbl_ana_Articles]( [ID] [int] IDENTITY(1,1) NOT NULL, [ID_BRAND] [int] NOT NULL, [CODE] [nvarchar](40) NOT NULL, [CODFOR] [nvarchar](40) NOT NULL, [COD_ALT01] [nvarchar](50) NOT NULL, [COD_ALT02] [nvarchar](50) NOT NULL, [COD_ALT03] [nvarchar](50) NOT NULL, [ID_UOM] [int] NOT NULL, [IS_ACTIVE] [bit] NOT NULL, [_ATTRIBUTES] [nvarchar](max) NOT NULL, [_DOCUMENTS] [nvarchar](max) NOT NULL, [_SEO] [nvarchar](max) NOT NULL, [_TRANSLATIONS] [nvarchar](max) NOT NULL, [_TAGS] [nvarchar](max) NOT NULL, [_NOTES] [nvarchar](max) NOT NULL, [_METADATA] [nvarchar](max) NOT NULL, [IS_B2B] [bit] NOT NULL, [IS_B2C] [bit] NOT NULL, [IS_PROMO] [bit] NOT NULL, [IS_NEWS] [bit] NOT NULL, [CAN_BE_RETURNED] [bit] NOT NULL, [IS_SHIPPABLE] [bit] NOT NULL, [HAS_SHIPPING_COSTS] [bit] NOT NULL, [IS_PURCHEASABLE] [bit] NOT NULL, CONSTRAINT [PK_tbl_ana_articles] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO INSERT INTO [dbo].[tbl_ana_Articles] ([ID_BRAND] ,[CODE] ,[CODFOR] ,[COD_ALT01] ,[COD_ALT02] ,[COD_ALT03] ,[ID_UOM] ,[IS_ACTIVE] ,[_ATTRIBUTES] ,[_DOCUMENTS] ,[_SEO] ,[_TRANSLATIONS] ,[_TAGS] ,[_NOTES] ,[_METADATA] ,[IS_B2B] ,[IS_B2C] ,[IS_PROMO] ,[IS_NEWS] ,[CAN_BE_RETURNED] ,[IS_SHIPPABLE] ,[HAS_SHIPPING_COSTS] ,[IS_PURCHEASABLE]) VALUES (1 ,'COD1' ,'SUPPLIER1' ,'CATEGORY1' ,'CATEGORY1-BIS' ,'CATEGORY2' ,1 ,1 ,'{ "url" : "https://old.com " }' ,'' ,'' ,'' ,'' ,'' ,'' ,1 ,0 ,0 ,0 ,1 ,1 ,0 ,1); DECLARE @FullOldUrl AS NVARCHAR(50) = 'https://old.com '; DECLARE @FullNewUrl AS NVARCHAR(50) = 'https://new.com '; --ARTICLE PRINT('updating articles'); WHILE 1=1 BEGIN UPDATE TOP (8196) [dbo].[tbl_ana_Articles] SET [_ATTRIBUTES] = REPLACE([_ATTRIBUTES] , @FullOldUrl, @FullNewUrl) ,[_DOCUMENTS] = REPLACE([_DOCUMENTS] , @FullOldUrl, @FullNewUrl) ,[_SEO] = REPLACE([_SEO] , @FullOldUrl, @FullNewUrl) ,[_TRANSLATIONS] = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl) ,[_TAGS] = REPLACE([_TAGS] , @FullOldUrl, @FullNewUrl) ,[_NOTES] = REPLACE([_NOTES] , @FullOldUrl, @FullNewUrl) WHERE [_ATTRIBUTES] like '%' + @FullOldUrl + '%' OR [_DOCUMENTS] like '%' + @FullOldUrl + '%' OR [_SEO] like '%' + @FullOldUrl + '%' OR [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR [_TAGS] like '%' + @FullOldUrl + '%' OR [_NOTES] like '%' + @FullOldUrl + '%' IF (@@ROWCOUNT <= 0) BEGIN BREAK; END END SELECT * FROM [dbo].[tbl_ana_Articles] PRINT('Finished'); Here the execution plan produced by the toy example (i can't get the execution plan of real scenario). https://www.brentozar.com/pastetheplan/?id=SJhVTcMTo I am really puzzled about what caused that issue **--EDIT :** i have run the procedure again, and discovered that if i let run long enough (~30 min) i get the right behaviour. So apparently i have a performance issue here. I am not really intereseted in performance here, because the procedure is used only rarely (manually) during mantainance (only if the site change domain). But i am curious if I made some blunder here, to get such low performance

My database table named VmIpAddress has the following 12 columns id, hosting_id, server_id, vm_id, ip, mac_address, subnet_mask, gateway, cidr, trunks, tag, net A row might contain for example "3471","3654","16",NULL,"137.74.189.22","02:00:00:55:a1:57","255.255.255.224","137.74.189.254","27",NULL,NULL,"net0" I have a .txt file contain the following data 137.74.189.22, 193.15.125.110, 02:00:00:20:a2:10 137.74.189.23, 193.15.125.111, 02:00:00:20:a2:11 137.74.189.24, 193.15.125.112, 02:00:00:20:a2:12 137.74.189.25, 193.15.125.113, 02:00:00:20:a2:13 Note that the $5 "ip" must be exactly what the text file contains 137.74.189.22 in this case, there may also be 137.74.189.222 which I do not want changed. From this data the table row contains 137.74.189.22 should be modified to the new IP address and MAC as well as a few other columns $3 $5 $6 $7 $8 $9 should be changed so that it changes to "3471","3654","19",NULL,"193.15.125.110","02:00:00:20:a2:10","255.255.255.0","193.15.125.1","24",NULL,NULL,"net0" $3 = Always 19 $5 = First variable in text file $6 = Third variable in text file $7 = Always 255.255.255.0 $8 = Always 193.15.125.1 $9 = Always 24 OS Linux mysql 5.7

I have part of a string that I would like to remove when selecting. The Problem is that there are a few pieces within the string that changes. This is what I have so far. Select GROUP_CONCAT(DISTINCT Substr(Concat(Round(ms_cart.cart_qty), 'x', ms_cart.cart_sku), 1, 12), Concat(' | ', ms_cart_options.co_opt_name), Replace(ms_cart_options.co_opt_name, '

This is a buddy package. Who will the buddy photo be taken with

: THIS PART CHANGES

What team?: THIS PART CHANGES', '')) as Items FROM ms_cart INNER JOIN ms_orders ON ms_orders.order_id = ms_cart.cart_order INNER JOIN ms_cart_options ON ms_cart_options.co_cart_id=ms_cart.cart_id

I place the alias values in sql into a string sentence and constantly generate a new string with replace. But I have to do a lot of "replace" nested this way. The {{name}} in the string clause is actually the alias in the sql clause Is there a way to define the sql and template clause and automate it? Sample:

CREATE TABLE [dbo].[message](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[quantity] [float] NULL,
	[delivery] [datetime] NULL,
	[status] [tinyint] NULL,
)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Omer', 124, CAST(N'2022-10-18T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Jacob', 548, CAST(N'2022-11-05T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ([name], [quantity], [delivery], [status]) VALUES ( N'Hasan', 56454, CAST(N'2022-08-09T00:00:00.000' AS DateTime), 1)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Hans', 548, CAST(N'2023-11-01T00:00:00.000' AS DateTime), 0)

status
4 rows affected

select * from message where status=0

| id | name | quantity | delivery | status | | --:|:----|--------:|:--------|------:| | 1 | Omer | 124 | 2022-10-18 00:00:00.000 | 0 | | 2 | Jacob | 548 | 2022-11-05 00:00:00.000 | 0 | | 4 | Hans | 548 | 2023-11-01 00:00:00.000 | 0 |

declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

select 
replace(
replace(
replace(@template
,'{{name}}',name)
,'{{quantity}}',quantity)
,'{{delivery}}',format(delivery,'dd.MM.yyy'))
from message where status=0

| (No column name) | | :----------------| | Hello, Omer todays order quantity 124 this order must be sent by the latest 18.10.2022. | | Hello, Jacob todays order quantity 548 this order must be sent by the latest 05.11.2022. | | Hello, Hans todays order quantity 548 this order must be sent by the latest 01.11.2023. |

status
Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.

declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

declare @sqlString nvarchar(max)='select name,quantity,format(delivery,''dd.MM.yyy'') as [delivery] from message where status=0'
print @sqlString

status
Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.
select name,quantity,format(delivery,'dd.MM.yyy') as [delivery] from message where status=0

/*
template embed code

*/

[fiddle](https://dbfiddle.uk/amAM01de)

To store emails list I have two quite simple tables, domain and mailbox, so **user@domain.com** is stored as: **domain** | id | domain_name | |----|-------------| | 1 | domain.com | **mailbox** | id | local_part | domain_id | |----|------------|-----------| | 1 | user | 1 | (*local_part* is email part to the left of '@' sign, and *domain_id* refers to id value of domain in **domain** table). Until this point, all is simple, and I can **list all boxes** like this:

SELECT CONCAT(m.local_part, '@', d.domain_name) 
FROM domain d, mailbox m 
WHERE m.domain_id=d.id

Now I need to add some way to support domain alias (so e.g. domain.net become alias of domain.com). So I create table domain_alias: | domain_old | domain_new | |-------------|------------| | domain.com | domain.net | Now I try to modify above query so all boxes in domain_old be shown as domain_new boxes (e.g. instead of user@domain.com this query yields user@domain.net). I suspect this might be not that hard but I must be missed something so please advice! P.S. I do that with MariaDB 10.3.

I inherited a system in which all updates (even to a single row/record) to MySQL tables are not done using UPDATE table_name SET. Rather, they are done by: 1. Exporting the existing table to a CSV (text) file. 2. Modifying the corresponding row(s) in the CSV file. 3. Reloading the CSV file using LOAD DATA ... REPLACE INTO TABLE. This is the first time I see such an approach to updating table records and I am wondering what could be the rational for that. BTW, this scheme results in numerous thread synchronization issues because of the need to lock CSV files while updating. I would appreciate an explanation or insights on the benefits of using LOAD DATA ... REPLACE INTO TABLE instead of UPDATE table_name SET.

I have a column of urls with some of the urls like www.google.com/// Need them to be www.google.com/ instead.

I have set up a 2-node PG 9.4 system with BDR. Replication works great. Now, let's suppose that one of the two nodes needs to be replaced. In fact, let's be more specific. Let's say that the node on which I ran the command to create the group : SELECT bdr.bdr_group_create() has to be replaced. What is the procedure to do this? Thanks