Funny Test II - SQL Server Forums

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

Site Related Forums

The Yak Corral

Funny Test II

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 20:10:18

Parse out the page name from the urls provided.

Use only:

Using 1 select

no subqueries

only 3 function calls (left(),Left(),right() counts as three)


Declare @myTable table (url nvarchar(1000), page nvarchar(100))

Insert Into @myTable (url)
Select 'http://www.site.com/rootPage.asp'
Union All Select 'http://www.site.com/page1.asp'
Union All Select 'http://www.site1.com/subDir1/page2.htm'
Union All Select 'http://www.site1.com/subDir1/subDir2/subDir3/subDir4/page3.html'

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 20:25:39

How about

Update @myTable
SET page = Ltrim( Right(replace(url,'/',space(255)),100)) from @myTable

Note: Since I can't use space(255) because it's a 4th function, you need to actually type in 255 spaces :)
I see if I can come up with anything else.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-05 : 20:47:03

SELECT REVERSE( SUBSTRING(REVERSE(URL), 1, CHARINDEX('/', REVERSE(URL), 1) -1) FROM MyTable

Five function calls ! Argh !

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-05 : 20:47:19

am I missing something?

select right(url, charindex('/',reverse(url))-1) from @myTable

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-09-05 : 20:55:34

We have a winner

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-05 : 21:02:00

OK I've got a new one

Using only one select statement, and only 2 basic SQL functions, produce a wysiwyg word processor....

bet you can't.. oh go on and try!

[edit]
OK - not funny - I'll stick to what I know

There once was an SQL knight
Who took a particular delight
in framing a test
for others to best
but nobody got it quite right....
(except one guy, but he made a really dumb joke that noone liked and so his didn't count)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-09-05 : 21:09:53

Exec master..xp_cmdshell 'winword.exe'

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-05 : 21:18:47

I was waiting for that

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 21:20:53

I was busy trying how to figure out how to make it start on your machine.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 21:23:21

that seems like it was too easy...I really didn't think reverse would show up so fast.

Good job! and nice poem!

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-05 : 21:27:16

What is the simplest statement which will parse the page name and remove a query string (if present).

http://site.com/path/filename.asp

http://site.com/path/filename.asp?Querystring=abcde

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-05 : 21:29:25

Those links don't work for me...

oh, I get it - humour...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-05 : 21:34:25

I need to parse a simple filename myself, but the requirement is to leave the subfolder on the file name. For example:

/Folder1/Folder2/filename.asp
or
/FolderA/FolderB/Folder2/filename.asp

I need to retrieve

/Folder2/filename.asp

Then, there's the possability of a querystring being present. It needs to be removed.

?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 21:42:10

Does this work...


Declare @myTable table (url nvarchar(1000), page nvarchar(100))

Insert Into @myTable (url)
Select 'http://site.com/path/filename1.asp'
Union All Select 'http://site.com/path1/path2/filename2.asp?Querystring=abcde'


Select page = substring(url,charindex('/',url,patIndex('%.%/%',url)),len(url)-charindex('/',url,patIndex('%.%/%',url))-charindex('?',reverse(url))+1)
From @myTable

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-10 : 07:01:20

Corey, thanks for this post.

When I run it, I get

/path1/path2/filename2.asp

for the 2nd URL but what I need is

/path2/filename2.asp

Sam

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 07:16:44

oh! you only want the last directory?


Declare @myTable table (url nvarchar(1000), page nvarchar(100))

Insert Into @myTable (url)
Select 'http://site.com/path/filename1.asp'
Union All Select 'http://site.com/path1/path2/filename2.asp?Querystring=abcde'

Select 
	fullPath = page,
	shortPath = right(page,charindex('/',reverse(page),charindex('/',reverse(page))+1))
From 
(Select page = substring(url,charindex('/',url,patIndex('%.%/%',url)),len(url)-charindex('/',url,patIndex('%.%/%',url))-charindex('?',reverse(url))+1) From @myTable ) A

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-10 : 07:49:46

Perfect!

Now, if the querystring is m=xxxx I need to keep it on the pathname. (xxxx is any simple alphabetic string without special characters [a-z])

Declare @myTable table (url nvarchar(1000), page nvarchar(100))

Insert Into @myTable (url)
Select 'http://site.com/path/filename1.asp?nope=exclude'
Union All Select 'http://site.com/path1/path2/filename2.asp?m=tvl&drink=expresso'
Union All Select 'http://site.com/path1/path2/filename2.asp?m=rpt'

Select
fullPath = page,
shortPath = right(page,charindex('/',reverse(page),charindex('/',reverse(page))+1))
From
(Select page = substring(url,charindex('/',url,patIndex('%.%/%',url)),len(url)-charindex('/',url,patIndex('%.%/%',url))-charindex('?',reverse(url))+1) From @myTable ) A

Should return

/path/filename1.asp
/path2/filename2.asp?m=tvl
/path2/filename2.asp?m=rpt

That's about it. If you can do this, I'll embarrass myself by posting the UDF I had written to do this job. If it is any help, when m=xxx appears, filename2.asp is a fixed and unique filename.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 07:56:23

Here you go...


Declare @myTable table (url nvarchar(1000), page nvarchar(100))

Insert Into @myTable (url)
Select 'http://site.com/path/filename1.asp?nope=exclude'
Union All Select 'http://site.com/path1/path2/filename2.asp?m=tvl&drink=expresso'
Union All Select 'http://site.com/path1/path2/filename2.asp?m=rpt'

Select 
	fullPath = page + qStr,
	shortPath = right(page,charindex('/',reverse(page),charindex('/',reverse(page))+1)) + qStr
From 
(Select 
	page = substring(url,charindex('/',url,patIndex('%.%/%',url)),len(url)-charindex('/',url,patIndex('%.%/%',url))-charindex('?',reverse(url))+1), 
	qStr = case when patindex('%?m=___%',url)>0 then substring(url,patindex('%?m=___%',url),6) else '' end 
From @myTable ) A

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-10 : 08:04:33

Corey,

You're the patindex stud. Interesting execution plan. It's about 0% for everything.

Here's a piece of what I had that returned the simple filename. I don't enjoy throwing up inferior stuff, but I figure it's the pennance I owe for this gift. In my defense, this UDF is a couple of years old. If I remember correctly, part of the comparison is handled outside the UDF somewhere:

CREATE FUNCTION dbo.GetFileNameFromUrl (@Url varchar (500))
RETURNS varchar (255)
AS
BEGIN
DECLARE @filename varchar (100), @idot int, @qdot int

-- ELIMINATE ANY QUERYSTRING ON THE URL
SET @idot = charindex('?', @Url) -1 -- Find the Querystring
If @idot <= 0 BEGIN -- No querystring if 0
	SET @idot = LEN(@Url) -- No query string
	IF @idot = 0 RETURN('') -- Return nothing if no string
END
ELSE BEGIN -- Remove the query string from the URL
	IF	'?m=' <> SUBSTRING(@Url, @idot+1, 3) BEGIN -- Retain the XML querystring ?m=moduleabbr
		SET	@Url = LEFT (@Url, @idot) -- Remove query string
	END
END

-- REMOVE THE PATH, LEAVING THE 1 level path: ../path/SimpleFilename
-- LOCATE THE LAST FORWARDSLASH
SET @Url  = REVERSE(@Url) -- Got to scan from the back
SET @idot = charindex('/', @Url) -- Location of first backslash
SET @idot = charindex('/', SUBSTRING(@Url, @idot+1, 300) ) + @idot -- Find the location of the 2nd backslash
SET @Url  = LEFT(@Url, @idot) + '..' -- Need to form valid relative address

RETURN( REVERSE(@Url) )

END
GO

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-10 : 08:13:04

the m=___ addresses any 3 character parameter.

Can your solution be modified to handle variable length m=x or m=xx or m=xxxx ?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 08:13:09

In all honesty, I learned about patindex at SQL Team in the last month (maybe 2)... I was astounded that you could do that. I probably would have written something similar a few years ago (although I don't do well with a lot of variables). Isn't it odd how strange your old code looks when you revisit it??

Glad I could help!

Corey

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources