Questions tagged [replace]
84 questions
13
votes
3 answers
SQL Server: Replace with wildcards?
Does Microsoft SQL Server natively support some sort of replace function using wild cards? I gather that Regular Expressions are not available natively.
I note that there is a PATINDEX function which can be used to bolt together a solution — is…
Manngo
- 3,065
- 10
- 38
- 61
10
votes
3 answers
Replace special characters in a column with space
I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid):
SELECT columnA
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'
How can I…
Stackoverflowuser
- 1,550
- 3
- 27
- 42
9
votes
2 answers
Update many rows in a table with a single statement?
What is the easiest way to update many rows in a table? I have a csv file that looks like this:
|primary_key |value|
| 1 | xyz|
| 2 | abc|
| 3 | def|
...
Rows with these primary keys already exist in the target table…
David LeBauer
- 3,162
- 8
- 32
- 34
7
votes
2 answers
Advantage of "LOAD DATA ... REPLACE INTO TABLE" over "UPDATE table_name SET"
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:
Exporting the existing table to a CSV (text) file.
Modifying the corresponding row(s) in the…
WebViewer
- 173
- 5
5
votes
3 answers
How can you replace the last occurrence of a character in a string (text column)?
Let's say I have a text field with this value in Postgres:
'bar$foo$john$doe$xxx'
I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. After the replacement, the contents of the field…
Fmartinez
- 69
- 1
- 1
- 4
5
votes
1 answer
Query to find and replace text in all tables and fields of a mysql db
I need to run a query to find and replace some text in all tables of a mysql database.
I found this query, but it only looks for the text in the tbl_name table and just in the column field.
update tbl_name set column=REPLACE(column, 'fuschia',…
chefnelone
- 185
- 1
- 1
- 7
4
votes
1 answer
postgresql replace table without losing dependancies
i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the table without doing all the hard job? Like a CREATE…
Ben
- 267
- 5
- 11
4
votes
4 answers
Multiple string replacements
I've got a replacement map table
CREATE TABLE #ReplacementMap (old NVARCHAR(10), new NVARCHAR(10))
INSERT INTO #ReplacementMap VALUES ('A',5)
INSERT INTO #ReplacementMap VALUES ('C',9)
INSERT INTO #ReplacementMap VALUES ('D',4)
and a table of…
Przemyslaw Wojda
- 303
- 1
- 4
- 13
4
votes
2 answers
How to split numbers and text in MySQL
I have searched over the internet looking for a function like REGEXP_REPLACE in Oracle, regexp_replace in PostgresSQL but I haven't find one similar in MySQL just REGEXP and RLIKE, but these operators just check whether the string matches pattern…
oNare
- 3,231
- 2
- 22
- 35
4
votes
1 answer
Mysql REPLACE INTO query for Multiple rows insertion
I'm trying to add multiple rows using 'replace into' , but its updating one row.
REPLACE INTO user_balance
(user_id,acc_type,currency,balance,enable_deposit,
enable_withdrawal,pending_balance,update_ip)
VALUES …
aeroarunn
- 41
- 1
- 1
- 2
3
votes
1 answer
Does the Sql Server REPLACE function increase the estimated row size?
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…
Alessandro Mortola
- 83
- 3
3
votes
1 answer
PostgreSQL 11: Replace function body by users from the same group
In the documentation of CREATE FUNCTION is stated that ''You must own the function to replace it (this includes being a member of the owning role).''
Two questions:
I don't understand what exactly the ''this includes being a member of the owning…
Radu Dumbrăveanu
- 437
- 1
- 8
- 13
3
votes
2 answers
Remove the last character from Variable String
I have a few variable data in the column "email".
For example:
roy2087@gmail.com123
sg123@yahoo.compori
How do I get rid of the characters after .com?
I used Replace(email,'.com%',gmail.com), however, we cannot use wildcard characters in…
Roy
- 31
- 1
- 1
- 2
3
votes
1 answer
Replace all Numeric Values with Single Character
I have a value in a SQL table like the below:
80400365_SwapOpenTrd_20180831.csv
I want to replace the numeric values so it is returned as this:
%_SwapOpenTrd_%.csv
I tried the below code but there are groups of % characters…
Kevin
- 533
- 2
- 12
- 20
3
votes
2 answers
How do I swap tables atomically in Oracle?
How to do I swap two tables in an atomic fashion in Oracle?
I would like to perform the equivalent of:
ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;
but what happens if a query needs table foo in between those two lines when there…
Hans Deragon
- 185
- 1
- 3
- 9