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.
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 > 0BEGIN 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. |
 |
|
|
|
|
|
|