SQL Replace Pattern - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Replace Pattern

Author

Topic

sh0wtym3
Starting Member

9 Posts

Posted - 2010-12-10 : 13:50:09

Hey, I'm looking to replace/update all instances of a state abbreviation. They currently look like this:


<span class="region">CA
<span class="region">FL
<span class="region">NY

... etc, etc. But there is no closing <span> tag, so I want to update these abbreviations to be:


<span class="region">CA</span>
<span class="region">FL</span>
<span class="region">NY</span>

... etc, etc. How can I do this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-10 : 14:01:27

[code]UPDATE yourTable
SET columnName = columnName + '</span>'
WHERE columnName like '<span class="region">%';
GO[/code]

sh0wtym3
Starting Member

9 Posts

Posted - 2010-12-10 : 14:05:43

Sorry I should have added that there is a bunch of information before and after the state abbreviations, here's (some) of the information before and after the abbreviations:


<span class="locality">Danville</span>, <span class="region">CA 94526

<span class="locality">Tallahassee</span>, <span class="region">FL 32303

etc, etc

sh0wtym3
Starting Member

9 Posts

Posted - 2010-12-10 : 14:07:03

Basically I want to find all instances of:

<span class="region">

... then skip TWO characters and add:

</span>

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-10 : 14:38:08

How's this?

UPDATE	t1
SET	tagList = REPLACE(tagList,
			left(tagList, charindex('class="region">', tagList) + 16),
			left(tagList, charindex('class="region">', tagList) + 16) + '</span>'
	);
GO

sh0wtym3
Starting Member

9 Posts

Posted - 2010-12-10 : 15:02:54

Works like a charm! Thank you so much

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-10 : 15:10:29

You're welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources