Solved

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

Posted on 2010-11-11
2
497 Views
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):

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
Comment
Question by:ZuZuPetals
2 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
Comment Utility
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
 
LVL 2

Author Closing Comment

by:ZuZuPetals
Comment Utility
I never thought of using PatIndex().  Thanks for putting so much work into this!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Do you hate spam? I do, and I am willing to bet you do as well. I often wonder, though, "if people hate spam so much, why do they still post their email addresses on the web?" I'm not talking about a plain-text posting here. I am referring to the fa…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now