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
Go to Top of Page

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)

Go to Top of Page

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
Go to Top of Page

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)

Go to Top of Page

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
Go to Top of Page

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)

Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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)

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -