| Author |
 Topic  |
|
|
kamii47
Constraint Violating Yak Guru
335 Posts |
 Posted - 12/27/2012 : 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
Flowing Fount of Yak Knowledge
India
1423 Posts |
Posted - 12/27/2012 : 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
335 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1423 Posts |
Posted - 12/27/2012 : 05:23:42
|
Try this...
SELECT DISTINCT link
FROM [dbo].[MYTable]
CROSS APPLY dbo.GetLinks([MYContents])
--
Chandu |
|
|
|
kamii47
Constraint Violating Yak Guru
335 Posts |
Posted - 12/27/2012 : 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
Flowing Fount of Yak Knowledge
India
1423 Posts |
Posted - 12/27/2012 : 06:24:12
|
Somewhere this value (@endpos - @strtpos) is getting negative..
SO check condition for @endpos >= @strtpos
--
Chandu |
|
|
|
kamii47
Constraint Violating Yak Guru
335 Posts |
Posted - 12/27/2012 : 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
Flowing Fount of Yak Knowledge
India
1423 Posts |
Posted - 12/28/2012 : 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
335 Posts |
Posted - 12/28/2012 : 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)
|
Edited by - kamii47 on 12/28/2012 06:59:05 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1423 Posts |
Posted - 12/28/2012 : 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
335 Posts |
Posted - 12/28/2012 : 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
Flowing Fount of Yak Knowledge
India
1423 Posts |
Posted - 12/28/2012 : 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 |
|
|
| |
Topic |
|
Select distinct Data with certain text
