Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 563
  • Last Modified:

T-SQL string manipulation without using Loop? T-SQL support regular expressions? (SQL Server 2005)

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 (
    id int,
    tracks varchar(4000)   
)

Inefficient Version:
declare @counter int
set @counter = 1
while @counter <= 10
begin
	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
end

Open in new window

0
ZuZuPetals
Asked:
ZuZuPetals
1 Solution
 
Brendt HessSenior DBACommented:
The difficulty with this one was the possibility of the 10 value.  Here is one solution I came up with:


CREATE TABLE #t (
	id int IDENTITY,
	tracks varchar(50)
	)
INSERT INTO #t(tracks) VALUES ('ewin 0. DISC 1: asdoab')
INSERT INTO #t(tracks) VALUES ('0. DISC 2:')
INSERT INTO #t(tracks) VALUES ('0. DISC 3:a sdfabsuif')
INSERT INTO #t(tracks) VALUES ('0. DISC 4:')
INSERT INTO #t(tracks) VALUES ('asnwr 0. DISC 5:')
INSERT INTO #t(tracks) VALUES ('0. DISC 6:')
INSERT INTO #t(tracks) VALUES ('0. DISC 7:')
INSERT INTO #t(tracks) VALUES ('0. DISC 8:')
INSERT INTO #t(tracks) VALUES ('0. DISC 9:')
INSERT INTO #t(tracks) VALUES ('0. DISC 10:')
INSERT INTO #t(tracks) VALUES ('0. DISC 191:');

WITH tfx AS (
SELECT 
	id,
	PATINDEX('%0. DISC [123456789]:%', tracks) o9,
	PATINDEX('%0. DISC 10:%', tracks) o10
FROM #t
WHERE tracks LIKE '%0. DISC [123456789]:%'
	OR tracks LIKE '%0. DISC 10:%'
)
UPDATE #t
SET tracks = 
	CASE 
		WHEN o9 = 0
			THEN
				CASE
					WHEN o10 = 1
						THEN ''
					ELSE LEFT(tracks, o10-1)
				END + '<strong>' + SUBSTRING(tracks, o10+3, 8) + '</strong>' + SUBSTRING(tracks, o10+11, LEN(tracks))
		ELSE 
			CASE
				WHEN o9 = 1
					THEN ''
				ELSE LEFT(tracks, o9-1)
			END + '<strong>' + SUBSTRING(tracks, o9+3, 7) + '</strong>' + SUBSTRING(tracks, o9+10, LEN(tracks))
	END
FROM #t 
INNER JOIN tfx
	ON tfx.id = #t.id

Open in new window

0
 
ZuZuPetalsAuthor Commented:
I never thought of using PatIndex().  Thanks for putting so much work into this!!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now