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

Posted on 2010-11-11
Last Modified: 2012-06-27
I'm trying to write an efficient SQL statement to do the following string replacement on a single column in a table (see blow):

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
	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

Open in new window

Question by:ZuZuPetals
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 32

Accepted Solution

bhess1 earned 500 total points
ID: 34114364
The difficulty with this one was the possibility of the 10 value.  Here is one solution I came up with:

	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 (
	PATINDEX('%0. DISC [123456789]:%', tracks) o9,
	PATINDEX('%0. DISC 10:%', tracks) o10
WHERE tracks LIKE '%0. DISC [123456789]:%'
	OR tracks LIKE '%0. DISC 10:%'
SET tracks = 
		WHEN o9 = 0
					WHEN o10 = 1
						THEN ''
					ELSE LEFT(tracks, o10-1)
				END + '<strong>' + SUBSTRING(tracks, o10+3, 8) + '</strong>' + SUBSTRING(tracks, o10+11, LEN(tracks))
				WHEN o9 = 1
					THEN ''
				ELSE LEFT(tracks, o9-1)
			END + '<strong>' + SUBSTRING(tracks, o9+3, 7) + '</strong>' + SUBSTRING(tracks, o9+10, LEN(tracks))
FROM #t 
	ON =

Open in new window


Author Closing Comment

ID: 34122599
I never thought of using PatIndex().  Thanks for putting so much work into this!!!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 40
Select question from MySQL 1 20
Regex - Capture everything between two strings. 8 26
How to keep a record with the highest value 3 39
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question