What is the best way to replace all '<' with <
in a given database column? Basically perform s/<[^;]/</gi
Notes:
<;;;;;;;;;
)Yes, the results in the result pane can quickly be turned into HTML or SQL Insertion code using a RegEx search and replace, though this is often easiest done in a more specialised programmers' text editor.
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive.
On the Edit menu, point to Find and Replace, and then click Quick Replace to open the dialog box with both find options and replace options. Toolbar buttons and shortcut keys are also available to open the Find and Replace dialog box.
Some hacking required but we can do this with LIKE, PATINDEX, LEFT AND RIGHT and good old string concatenation.
create table test
(
id int identity(1, 1) not null,
val varchar(25) not null
)
insert into test values ('< <- ok, < <- nok')
while 1 = 1
begin
update test
set val = left(val, patindex('%<[^;]%', val) - 1) +
'<' +
right(val, len(val) - patindex('%<[^;]%', val) - 2)
from test
where val like '%<[^;]%'
IF @@ROWCOUNT = 0 BREAK
end
select * from test
Better is that this is SQL Server version agnostic and should work just fine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With