SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select distinct Data with certain text
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

351 Posts

Posted - 12/27/2012 :  02:37:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/27/2012 :  02:51:48  Show Profile  Reply with Quote
-- 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

351 Posts

Posted - 12/27/2012 :  03:05:09  Show Profile  Reply with Quote
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
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c6da7ac1-d102-482f-a2d6-fcbb17c21f70

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


Edited by - kamii47 on 12/27/2012 04:57:25
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/27/2012 :  05:23:42  Show Profile  Reply with Quote
Try this...

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


--
Chandu
Go to Top of Page

kamii47
Constraint Violating Yak Guru

351 Posts

Posted - 12/27/2012 :  05:43:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/27/2012 :  06:24:12  Show Profile  Reply with Quote
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

351 Posts

Posted - 12/27/2012 :  09:45:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/28/2012 :  00:54:28  Show Profile  Reply with Quote
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

351 Posts

Posted - 12/28/2012 :  06:07:23  Show Profile  Reply with Quote
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)

Edited by - kamii47 on 12/28/2012 06:59:05
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/28/2012 :  07:01:49  Show Profile  Reply with Quote
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

351 Posts

Posted - 12/28/2012 :  07:08:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/28/2012 :  08:18:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.75 seconds. Powered By: Snitz Forums 2000