如何仅从SQL Server db检索文本 [英] How to retrieve text only from SQL server db
本文介绍了如何仅从SQL Server db检索文本的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
包含粗体,斜体,强标签以及文本的数据。如何仅从该数据中检索文本。数据如下面db中所示。
现在我只想用它包含的正确标签格式检索文本。
我尝试了什么:
< p>< strong>您有资格获得雇主提供的膳食娱乐卡和/或生活费卡!< / strong>< / p>
解决方案
你没有说你正在使用什么版本的SQL服务器,所以这里有一些我发现的东西适用于SQL Server 2005.在较新版本的DBMS中可能有更好的方法。
创建功能[dbo]。[udf_StripHTML ]
(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start int
DECLARE @End int
DECLARE @Length int
- 用'''''替换HTML实体haracter
SET @Start = CHARINDEX('',@ HTMLText)
SET @End = @Start + 5
SET @Length =(@ End - @Start)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@ Length,'')
SET @Start = CHARINDEX('',@ HTMLText)
SET @End = @Start + 5
SET @Length =(@ End - @Start)+ 1
END
- 替换HTML实体&使用'&'字符(这需要先完成,因为
- '&'可能被双重编码为'& amp;')
SET @Start = CHARINDEX('& ;',@ HTMLText)
SET @End = @Start + 4
SET @Length =(@ End - @Start)+ 1
WHILE(@Start> 0 AND @End> 0 AND @Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@ Length,'&')
SET @Start = CHARINDEX('& ;',@ HTMLText)
SET @End = @Start + 4
SET @Length =(@ End - @Start)+ 1
END
- 替换HTML实体<使用'<'字符
SET @Start = CHARINDEX('<',@ HTMLText)
SET @End = @Start + 3
SET @Length =(@ End - @开始)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@长度,'<')
SET @Start = CHARINDEX('<',@ HTMLText)
SET @End = @Start + 3
SET @Length =(@ End - @开始)+ 1
END
- 替换HTML实体>使用'>'字符
SET @Start = CHARINDEX('>',@ HTMLText)
SET @End = @Start + 3
SET @Length =(@ End - @开始)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@长度,'>')
SET @Start = CHARINDEX('>',@ HTMLText)
SET @End = @Start + 3
SET @Length =(@ End - @开始)+ 1
END
- 替换HTML实体&使用'&'字符
SET @Start = CHARINDEX('& amp;',@ HTMLText)
SET @End = @Start + 4
SET @Length =(@End - @Start)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start ,@ Length,'&')
SET @Start = CHARINDEX('& amp;',@ HTMLText)
SET @End = @Start + 4
SET @Length =( @End - @Start)+ 1
END
- 替换HTML实体 使用''字符
SET @Start = CHARINDEX(' ',@ HTMLText)
SET @End = @Start + 5
SET @Length =(@ End - @Start) + 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@ Length, '')
SET @Start = CHARINDEX(' ',@ HTMLText)
SET @End = @Start + 5
SET @Length =(@ End - @Start)+ 1
END
- 替换任何< br>带换行符的标签
SET @Start = CHARINDEX('< br>',@ HTMLText)
SET @End = @Start + 3
SET @Length =(@ End - @Start )+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@ Length ,CHAR(13)+ CHAR(10))
SET @Start = CHARINDEX('< br>',@ HTMLText)
SET @End = @Start + 3
SET @Length =(@End - @Start)+ 1
END
- 替换任何< br />带换行符的标签
SET @Start = CHARINDEX('< br />',@ HTFText)
SET @End = @Start + 4
SET @Length =(@ End - @Start)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start, @Length,CHAR(13)+ CHAR(10))
SET @Start = CHARINDEX('< br />',@ HTFText)
SET @End = @Start + 4
SET @Length =(@ End - @Start)+ 1
END
- 替换任何< br />带换行符的标签
SET @Start = CHARINDEX('< br />',@ HTFText)
SET @End = @Start + 5
SET @Length =(@ End - @Start)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start, @Length,CHAR(13)+ CHAR(10))
SET @Start = CHARINDEX('< br />',@ HTFText)
SET @End = @Start + 5
SET @Length =(@ End - @Start)+ 1
END
- 删除< STYLE>之间的任何内容标签
SET @Start = CHARINDEX('< STYLE',@ HTTText)
SET @End = CHARINDEX('< / STYLE>',@ HTMLText,CHARINDEX('<',@ HTMLText ))+ 7
SET @Length =(@ End - @Start)+ 1
WHILE(@Start> 0和@End> 0和@Length> 0)BEGIN
SET @HTMLText = STUFF(@HTMLText,@ Start,@ Length,'')
SET @Start = CHARINDEX('< STYLE',@ HTFText)
SET @End = CHARINDEX ('< / STYLE>',@ HTMLText,CHARINDEX('< / STYLE>',@ HTMLText))+ 7
SET @Length =(@ End - @Start)+ 1
END
- 删除< whatever>之间的任何内容标签
SET @Start = CHARINDEX('<',@ HTMLText)
SET @End = CHARINDEX('>',@ HTMLText,CHARINDEX('<',@ HTMLText))
SET @Length =(@ End - @Start)+ 1
WHILE(@Start> 0和@End> 0 AND @Length> 0)BEGIN
SET @ HTMLText = STUFF(@HTMLText,@ Start,@ Length,'')
SET @Start = CHARINDEX('<',@ HTFText)
SET @End = CHARINDEX('>',@ HTMLText,CHARINDEX('<',@ HTMLText))
SET @Length =(@ End - @Start)+ 1
END
RETURN LTRIM(RTRIM(@HTMLText) ))
END
创建表
创建表HtmlTags
(htmlText nvarchar(max))
插入带有HTML值的文字
插入HtmlTags值('< p>< strong>您有资格获得雇主的膳食娱乐卡和/或生活费用卡!< / strong>< / p>')
SQL SELECT查询仅检索文本
将@htmlText声明为varchar(max)
选择@htmlText = REPLACE(htmlText,'< p>< strong>' ,'')来自HtmlTags
选择@htmlText = REPLACE(@htmlText,'< / strong>< / p>','')
print @htmlText
Data containing with bold,Italic,strong tags along with text.How to retrieve text only from that data.The data is like given below in db.
Now i want to retrieve the text only with proper tag format which it contains.
What I have tried:
<p><strong>You are eligible for a Meal Entertainment card and/or a Living Expense card from your Employer!</strong></p>
解决方案
You don't say what version of SQL server you're using, so here's something I found a while back that works on SQL Server 2005. There may be a better way in newer versions of the DBMS.
CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText varchar(MAX) ) RETURNS varchar(MAX) AS BEGIN DECLARE @Start int DECLARE @End int DECLARE @Length int -- Replace the HTML entity " with the '"' character SET @Start = CHARINDEX('"', @HTMLText) SET @End = @Start + 5 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '"') SET @Start = CHARINDEX('"', @HTMLText) SET @End = @Start + 5 SET @Length = (@End - @Start) + 1 END -- Replace the HTML entity & with the '&' character (this needs to be done first, as -- '&' might be double encoded as '&') SET @Start = CHARINDEX('&', @HTMLText) SET @End = @Start + 4 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&') SET @Start = CHARINDEX('&', @HTMLText) SET @End = @Start + 4 SET @Length = (@End - @Start) + 1 END -- Replace the HTML entity < with the '<' character SET @Start = CHARINDEX('<', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '<') SET @Start = CHARINDEX('<', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 END -- Replace the HTML entity > with the '>' character SET @Start = CHARINDEX('>', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '>') SET @Start = CHARINDEX('>', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 END -- Replace the HTML entity & with the '&' character SET @Start = CHARINDEX('&', @HTMLText) SET @End = @Start + 4 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&') SET @Start = CHARINDEX('&', @HTMLText) SET @End = @Start + 4 SET @Length = (@End - @Start) + 1 END -- Replace the HTML entity with the ' ' character SET @Start = CHARINDEX(' ', @HTMLText) SET @End = @Start + 5 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ' ') SET @Start = CHARINDEX(' ', @HTMLText) SET @End = @Start + 5 SET @Length = (@End - @Start) + 1 END -- Replace any <br> tags with a newline SET @Start = CHARINDEX('<br>', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10)) SET @Start = CHARINDEX('<br>', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 END -- Replace any <br/> tags with a newline SET @Start = CHARINDEX('<br/>', @HTMLText) SET @End = @Start + 4 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10)) SET @Start = CHARINDEX('<br/>', @HTMLText) SET @End = @Start + 4 SET @Length = (@End - @Start) + 1 END -- Replace any <br /> tags with a newline SET @Start = CHARINDEX('<br />', @HTMLText) SET @End = @Start + 5 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10)) SET @Start = CHARINDEX('<br />', @HTMLText) SET @End = @Start + 5 SET @Length = (@End - @Start) + 1 END -- Remove anything between <STYLE> tags SET @Start = CHARINDEX('<STYLE', @HTMLText) SET @End = CHARINDEX('</STYLE>', @HTMLText, CHARINDEX('<', @HTMLText)) + 7 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '') SET @Start = CHARINDEX('<STYLE', @HTMLText) SET @End = CHARINDEX('</STYLE>', @HTMLText, CHARINDEX('</STYLE>', @HTMLText)) + 7 SET @Length = (@End - @Start) + 1 END -- Remove anything between <whatever> tags SET @Start = CHARINDEX('<', @HTMLText) SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText)) SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '') SET @Start = CHARINDEX('<', @HTMLText) SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText)) SET @Length = (@End - @Start) + 1 END RETURN LTRIM(RTRIM(@HTMLText)) END
Created Table
create table HtmlTags (htmlText nvarchar(max))
insert text with HTML values
insert into HtmlTags values ('<p><strong>You are eligible for a Meal Entertainment card and/or a Living Expense card from your Employer!</strong></p>')
SQL SELECT query to retrieve text only
declare @htmlText as varchar(max) Select @htmlText = REPLACE(htmlText,'<p><strong>','') from HtmlTags Select @htmlText = REPLACE(@htmlText,'</strong></p>','') print @htmlText
这篇关于如何仅从SQL Server db检索文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文