Select distinct Data with certain text

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)

Select distinct Data with certain text

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-27 : 02:37:20

I have a content in which i have certain page name with ended with .aspx

href="http://www.mysite.com/MyPage.aspx"

I wanted to list of all such distinct entries in all of my content

how can i retrieve them.

At the moment it simply selected all content
SELECT [MYContents]
FROM [dbo].[MYTable]
WHERE [MYContents] LIKE '%.aspx%'

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-27 : 02:51:48

-- To avoid duplicate entries, use DISTINCT keyword
SELECT DISTINCT [MYContents]
FROM [dbo].[MYTable]
WHERE [MYContents] LIKE '%.aspx%'

--If u want to get only filename part from that Content
SELECT DISTINCT RIGHT([MYContents], CHARINDEX('/', REVERSE([MYContents]))-1 ) AS fileNames
FROM [dbo].[MYTable]
WHERE [MYContents] LIKE '%.aspx%'

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-27 : 03:05:09

I need to get full href="http://www.mysite.com/MyPage.aspx" entries with in the content whcih also have other body element.
I have minor help from
[url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c6da7ac1-d102-482f-a2d6-fcbb17c21f70[/url]

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-27 : 05:23:42

Try this...

SELECT DISTINCT link
FROM [dbo].[MYTable]
CROSS APPLY dbo.GetLinks([MYContents])

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-27 : 05:43:47

Thanks
May be some problem in function or data as i am getting error
Invalid length parameter passed to the SUBSTRING function.
Remember i may have multiple href

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-27 : 06:24:12

Somewhere this value (@endpos - @strtpos) is getting negative..
SO check condition for @endpos >= @strtpos

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-27 : 09:45:11

select z.* from
(select 'href="MyFirstPAge.aspx"

href="http://www.test.com/2009/05/aa-bb.html"

href="MySecondPage.aspx"' as content) x
cross apply dbo.GetLinks(x.content) z

is the sample of error data

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-28 : 00:54:28

Small modification


ALTER function [dbo].[GetLinks] (@t nvarchar(max))
	returns @Links table (link nvarchar(max))
as
begin
	declare @strtpos int = PATINDEX('%href="http%.aspx"%', @t)
	declare @endpos int = 0
	declare @lnk nvarchar(max)
	while @strtpos > 0 
	begin
		select  @endpos = PATINDEX('%.aspx"%', @t)+ 5
		IF @endpos >@strtpos
		BEGIN
		  SELECT @lnk = substring(@t ,@strtpos, @endpos - @strtpos)
		  ,@t= RIGHT (@t, len(@t) - @endpos)
		  ,@strtpos = PATINDEX('%href="http%.aspx"%', @t)
		 
		 insert @Links values(@lnk )
		 END 
		 ELSE BREAK
	end 
	return 
end
GO
--Test 
DECLARE @Tablevar TABLE(vars varchar(1000))
INSERT INTO @Tablevar VALUES
('scfbg vubvfswdhicosadsjiao"'),
('href="http://www.mysite.com/MyPage.aspx"'),
( 'http://www.mysite.com/MyPage.aspx'), ('I have a content in which i have certain page name with ended with .aspx
href="http://www.mysite.com/MyPage.aspx"
I wanted to list of all such distinct entries in all of my content
href="http://www2.mysite.com/My444Page.aspx" ') ,
('href="MyFirstPAge.aspx"
href="http://www.test.com/2009/05/aa-bb.html"
href="MySecondPage.aspx"' )

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-28 : 06:07:23

Nice check bandi but it didn't get the correct answer

Sample data is


<a shape="rect" href="http://www.qwewqe.fda.gov/wqewqe/pdf8/wewe.pdf" target="_blank">System</a> rket. </p>been calling for
 <a shape="rect" href="http://www.sdfdsf.org/news/sdfsdf/sdfsdfsdf.html" target="_blank">rocess</a>,in 2010,
 <a shape="rect" href="http://www.gfhgfh.com/defective-medical-devices/fghfgh-takes-aim-at-501k-fgh-device-approvals" 
 target="_blank"> changes. </p><p style="text-align: justify">The  <a shape="rect" href="fghfghfgh.aspx" target="_blank">
 substantially equivalent.</a> </p><p style="text-align: justify">Per th </p><p style="text-align: justify">market. </p>
 <p style="text-align: justify">The <a shape="rect" href="asdd.aspx" target="_blank">sub.</a> </p><p style="text-align: justify">
 Perhaps</p><p style="text-align: justify">t of a <a shape="rect" href="http://www.asdasdasd.com/view.aspx?rid=67920"
 target="_blank">one-in-eight</a> revision  </p><p style="text-align: justify">Critics de 
 <a shape="rect" href="http://www.sdfsdf.com/2009/05/fda-sdfsdf-devices-without-scrutiny-putting-dsf-at-risk.html"
 target="_blank">5 </p><p style="text-align: justify">We </p><p style="text-align: justify">If </p><p style="text-align: justify">
 <a shape="rect" href="SDFSDFSDF.aspx">sdfsdf</a> </p><p style="text-align: justify"><a shape="rect" href="asd324234dsdasd.aspx">asd</a>
 </p><p style="text-align: justify">  <a shape="rect" href="sadsad.aspx">sasdasdasd</a>

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-28 : 07:01:49

quote:
Originally posted by kamii47

Nice check bandi but it didn't get the correct answer
Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

what is the problem now?
In your sample data ('href="MyFirstPAge.aspx"
href="http://www.test.com/2009/05/aa-bb.html"
href="MySecondPage.aspx"'), there is no URL which is in the form of '%href="http%.aspx"%'

href="MyFirstPAge.aspx" --> Missing http
href="http://www.test.com/2009/05/aa-bb.html" --> Missing .aspx

href="MySecondPage.aspx" --> Missing http

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-28 : 07:08:31

Please check with the sample data. [may be we have posted almost same time]

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-28 : 08:18:51

yes there is a loop hole in that function..
we are checking for this pattern '%href="http%.aspx'.. right? Thats why it is taking starting from href="http and ending at .aspx

href="http://www.qwewqe.fda.gov/wqewqe/pdf8/wewe.pdf" target="_blank">System</a> rket. </p>been calling for
<a shape="rect" href="http://www.sdfdsf.org/news/sdfsdf/sdfsdfsdf.html" target="_blank">rocess</a>,in 2010,
<a shape="rect" href="http://www.gfhgfh.com/defective-medical-devices/fghfgh-takes-aim-at-501k-fgh-device-approvals"
target="_blank"> changes. </p><p style="text-align: justify">The <a shape="rect" href="fghfghfgh.aspx"

am trying to solve this.......

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources