how to replace a string between two characters ina

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)

how to replace a string between two characters ina

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2012-04-02 : 14:43:48

i have a table like the following

CREATE TABLE [dbo].[TITLE](
[ID] [int] NOT NULL,
[TITLE] [varchar](250) NOT NULL,
CONSTRAINT [pk_TITLE] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[EFF_BEG] ASC
)
) ON [PRIMARY]

insert into TITLE select 26698, '<b> Vertebroplasty, Vertebral Augmentation; Percutaneous #8722; 4S-153AB-R6</b>'
insert into TITLE select 26699, 'Drugs and Biologicals: Oxaliplatin (Eloxatin<sup> ®</sup>)'

i want to replace anything between '<' and '>' with '' (no space) and then replace the '<>' with '' (no space). i basically want TITLE to be 'Vertebroplasty, Vertebral Augmentation; Percutaneous #8722; 4S-153AB-R6'
'Drugs and Biologicals: Oxaliplatin (Eloxatin®)'

Any custom functions using Replace that would do this?

THanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-02 : 15:32:50

What I am about to suggest is a little iffy because it assumes that the string when put into an XML node will result in a well-formed XML fragment. That may not happen, for example, if you have unmatched pairs of XML tags. Regardless, here it goes:

SELECT
   CAST('<root>'+title+'</root>' AS XML).query('data(.)')
FROM
   Title;

jayram
Starting Member

47 Posts

Posted - 2012-04-02 : 16:06:36

Thanks!!!

but it looks like i have end tags not matching start tags and it throws an XML parsing error....

any other way....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-02 : 19:54:04

quote:
Originally posted by jayram

Thanks!!!

but it looks like i have end tags not matching start tags and it throws an XML parsing error....

any other way....

I was afraid of that!!

If you are doing it as a one-time thing, or infrequently, you could do something like the code below.

DECLARE @n INT = 1;
WHILE @n > 0
BEGIN
	UPDATE Title set title = 
		STUFF(title, PATINDEX('%<%>%',title), 1+CHARINDEX('>',title)-PATINDEX('%<%>%',title),'')
	WHERE CHARINDEX('>',title) > 0
	SET @n = @@ROWCOUNT;
END

But, if you need to do this frequently, or there are a large number of rows in the table, you may want to consider a CLR function. Take a look at this blog - it has examples of how to use CLR Regex and also code that you can download and install. Once you do that (and I have not tried to download or install it, so I don't know what issues you may run into), it is a simple call to the replace function like this:


UPDATE Title SET
   title = dbo.RegExReplace(title,'<.*?>','');

jayram
Starting Member

47 Posts

Posted - 2012-04-04 : 14:45:31

THank you!! i will read the log and try it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources