Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

353 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
2241 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

353 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
2241 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

353 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
2241 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

353 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
2241 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

353 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
2241 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

353 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
2241 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  
 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.06 seconds. Powered By: Snitz Forums 2000