I'm trying to write an efficient SQL statement to do the following string replacement on a single column in a table (see blow):
FIND THIS: 0. DISC *:
REPLACE WITH: <strong>DISC *:</strong>
where * is an integer from 1 to 10.
In other words, I wish to remove the "0." in front of the word "DISC" and make the actual disc number bold.
(There are other rows in the table that don't start with text "0. DISC" at all.)
There will be multiple occurrences of the search string in each row.
Any ideas for how to pull this off efficiently? My loop (below) runs just fine but is slow as molasses. Plus, it just feels "wrong" to use a loop! ;) I wish T-SQL nativity supported regular expressions.
I may end up taking an entirely different approach to solving this problem but before I do I was hoping someone had a quick solution in mind that could help me.
sample INPUT tracks row:
0. DISC 1: text here 0. DISC 2: text here 0. DISC 3: text here 1. some other data untouched 2. yet another row untouched
sample RESULT tracks row:
<strong>DISC 1:</strong> text here <strong>DISC 2:</strong> text here <strong>DISC 3:</strong> text here 1. some other data untouched 2. yet another row untouched
create table CompactDiscs (
declare @counter int
set @counter = 1
while @counter <= 10
print 'The counter is ' + cast(@counter as char)
update CompactDiscs set tracks=REPLACE(tracks,'0. DISC '+cast(@counter as varchar)+':','<strong>DISC '+cast(@counter as varchar)+'</strong>');
set @counter = @counter + 1