select first 50 characters after a delimiter

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 2008 Forums

Transact-SQL (2008)

select first 50 characters after a delimiter

Author

Topic

megala
Starting Member

23 Posts

Posted - 2009-05-09 : 04:29:14

Hi, I have a question. I have in a table which stored html tags with content. I would like to select for example the first 50 characters after the last tag. How can i achieve this?

I know about substring but this involve from delimiter .

example data in the field :

<h3>Top Fun</h3> 
Sunday, 8.30pm
Rated the mother of all programs in Taiwan

The result i want is Top Fun.Sunday, 8.30pm.Rated the mother of all programs in Taiwan. I need only the text without any tags. Is this possible?

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:41:42

will the format of html be consistent always?

megala
Starting Member

23 Posts

Posted - 2009-05-09 : 05:06:10

no it wont. each content is different and different tags.

megala
Starting Member

23 Posts

Posted - 2009-05-09 : 07:31:06

Hi, anyone have any idea on this? Is there any way we can truncate or replace tags?

kira
Starting Member

17 Posts

Posted - 2009-05-11 : 13:44:18

maybe you could write a UDF to deal with the problem, the algorithm looks like this:

declare @mystring varchar(8000)
set @mystring = '<h3>Top Fun</h3> 
Sunday, 8.30pm
Rated the mother of all programs in Taiwan'
declare @len int
declare @tmp varchar(8000)
declare @left int
declare @right int

set @tmp = ''
set @len = LEN(@mystring)
while @len > 1
begin
set @right = patindex('%>%',@mystring)
set @mystring = right(@mystring, @len - @right)
set @len = LEN(@mystring)
set @left = patindex('%<%',@mystring)

if(@left <> 0)
set @tmp = @tmp + left(@mystring, @left-1)
else
begin
set @tmp = @tmp + @mystring
set @mystring = ''
end

end
select @tmp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources