Search and Replace Part of String in Database

Search and replace part of string in database

I think 2 update calls should do

update VersionedFields
set Value = replace(value,'<iframe','<a><iframe')

update VersionedFields
set Value = replace(value,'> </iframe>','</a>')

UPDATE and REPLACE part of a string

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is type text or ntext you need to cast it to nvarchar

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

Search and replace part of string in database - what are the pitfalls?

Just create a test table, as a replica of your original source table, complete the update on there and check results.

You would want to do this as good SQL programming practice to ensure you don't mess up columns that should not be updated.

Another thing you can do is get a count of the records before hand that fit the criteria using a SELECT statement.

Run your update statement and if it's a 1-1 match on count, you should be good to go.

The only thing i can think of that would happen negatively in this respect is that additional columns get updated. Your WHERE clause is not specific for us to see, so there's no way to validate that what you're doing will do what you expect it to.

lookup and replace part of a string for all cells in a column using text in cells for a separate table as the lookup criteria

It is slippery slope with addresses, but this can help I believe:

Initial data:

DECLARE @tgt_address TABLE (ID INT IDENTITY(1,1),Name NVARCHAR(255),Address NVARCHAR(255));
INSERT INTO @tgt_address(Name,Address)VALUES
('John','123 State St Chester PA 25374')
,('Steve','798 Main Avn New York NY 21314')
,('Martha','981 West Ln Wilmington De 23142')
,('Mary','124 Main Street #2 Austin Tx 21432')
,('Timothy','25 Lark Thwy Maiami FL 12342')
;
DECLARE @Street_abbreviation TABLE (Commonly_used NVARCHAR(255),Abbreviation NVARCHAR(255));
INSERT INTO @Street_abbreviation(Commonly_used,Abbreviation)VALUES
('St','ST')
,('Avn','AVE')
,('Ln','LN')
,('#','APT')
,('Thwy','THROUGHWAY')
;

Not the best, but easy code:

SELECT t.Name,t.Address
,REPLACE(REPLACE(t.Address,'#','# '),' ' + a.Commonly_used + ' ',' ' + a.Abbreviation + ' ') AS [Result]
FROM @tgt_address t
INNER JOIN @Street_abbreviation a ON REPLACE(t.Address,'#','# ') LIKE '%[ ]' + a.Commonly_used + '[ ]%'
;

Smarter Code:

SELECT r.Name,r.Address,r.Result
FROM (
SELECT t.Name,t.Address
,REPLACE(REPLACE(t.Address,'#','# '),' ' + a.Commonly_used + ' ',' ' + a.Abbreviation + ' ') AS [Result]
,ROW_NUMBER()OVER(PARTITION BY t.Name,t.Address
ORDER BY CASE WHEN REPLACE(t.Address,'#','# ') LIKE '%[ ]' + a.Commonly_used + '[ ]%' THEN 0 ELSE 1 END) AS [rn]
FROM @tgt_address t
CROSS JOIN @Street_abbreviation a
) r
WHERE r.rn = 1
;

Apartments are tricky as they have no space around #

Find and Replace text in the entire table using a MySQL query

For a single table update

 UPDATE `table_name`
SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

From multiple tables-

If you want to edit from all tables, best way is to take the dump and then find/replace and upload it back.

Replace part of string in SQL

Quick and dirty but you could just do something like:

-- Update the end bits
UPDATE table
SET field = LEFT('field', LEN('field') -2) + '@24'
WHERE field LIKE '%@1';

-- Update the in between bits
UPDATE table
SET field = REPLACE(field, '@1|', '@24|')
WHERE field LIKE '%@1|%'; -- This where statement is optional due to the nature of the REPLACE.

Otherwise you'll have to look into the magical world of REGEX. If this is something you'd want to run more than once, I'd surely look into that. If this is just a one-time-fix-thingy, meh. It's thursday, I'd call it a valid excuse.

SQL - replace specific part of a string in multiple records

I don't think you necessarily need a regex replacement here. Just a normal replacement of download portal to download_portal should work. The following UPDATE should work on most databases:

UPDATE fruits
SET path = REPLACE(path, '/download portal/', '/download_portal/')
WHERE path LIKE '%/download portal/%'

Note: I think searching for /download portal/ is more restrictive, and safer, than just the plain text. This is so because it eliminates the chance of accidentally replacing download portal which appears as part of some other larger path name.

Replace part of string in mysql table column

UPDATE sometable SET somefield=REPLACE(somefield,'/wordpress//','/wordpress/');

Edit

@Kevin asked me to explain this query, so here we go:

  • I assume the basic UPDATE is clear: In all rows of sometable assign a new value to somefield
  • the REPLACE() function does exactly what it says: It replaces text. In our use case we ant it to take the old value of somefield, then replace all ocurrencies of '/wordpress//' with '/wordpress/'
  • these two parts taken together mean, in all rows of sometable assign somefield the value, that results, if you replace all ocurrencies of '/wordpress//' with '/wordpress/' in the old value.

SQL Server : find and replace part of a specific part of string that appears more than once in a string

Normalizing addresses can be a slippery slope. You may be surprised on the variations of Blvd. This is why I suggest you take a peek at Address standardization within a database

That mentioned, here is a simple and easy to expand option

Example

Declare @YourTable table (id int,Addr varchar(150))
Insert Into @YourTable values
(1,'123 Stripe St')
,(2,'555 SW Main St, Providence, RI')

Update @YourTable
Set Addr = ltrim(rtrim(
replace(
replace(' '+Addr+' ',' St ',' Street ')
,' St, ',' Street, ')
))

Select * from @YourTable

Returns

id  Addr
1 123 Stripe Street
2 555 SW Main Street, Providence, RI -- Notice middle St with a comma.

SQLite - replace part of a string

You can use the built in replace() function to perform a string replace in a query.

Other string manipulation functions (and more) are detailed in the SQLite core functions list

The following should point you in the right direction.

UPDATE table SET field = replace( field, 'C:\afolder\', 'C:\anewfolder\' ) WHERE field LIKE 'C:\afolder\%';



Related Topics



Leave a reply



Submit