help with query (control structure + regex)

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)

help with query (control structure + regex)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-10 : 15:58:58

Hi,

I have the following query I am bulding, its returning the desired results I am just really bad at control statements in TSQL.

What I want to do is dynamically pass the "and landingPage like '/products%' to the SPROC. If I pass nothing this part of the query is not added.

Also, I am wondering how difficult it would be to pass some RegEx into the "landingpage like '%asdf' " part of the query ?
I have started reading some articles but it seems pretty complicated. I am running sql2005 so I am clear on that part.

Any help is much appreicated!!

Thanks again,
mike123


CREATE PROCEDURE [dbo].[select_google_referrers_groupByQueryDate_OrderByQueryDate] 

(
 @numDays int,
 @landingPage varchar(50) = NULL
)
		 
AS SET NOCOUNT ON

	SELECT CONVERT(varchar(10),GO.queryDate,112) as referDate, 
	
	COUNT(GO.queryID) AS TotalReferrers FROM tblgoogle_referrerDetails  GO
	
	WHERE DateDiff(dd, GO.queryDate, GetDate()) < @numDays and landingpage like '/products%'

	GROUP BY CONVERT(varchar(10),GO.queryDate,112)
	
	ORDER BY referDate DESC
	GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 16:04:29

GO is a reserved word.

CREATE PROCEDURE [dbo].[select_google_referrers_groupByQueryDate_OrderByQueryDate] 
(
 @numDays int,
 @landingPage varchar(50) = NULL
)
		 
AS
SET NOCOUNT ON

SELECT CONVERT(varchar(10), gog.queryDate, 112) as referDate,
COUNT(*) AS TotalReferrers
FROM tblgoogle_referrerDetails as gog
WHERE gog.queryDate >= DateAdd(dd, - @numDays, GetDate())
and (@landingpage is null or gog.landingpage like @landingpage)
GROUP BY CONVERT(varchar(10), gog.queryDate, 112)
ORDER BY referDate DESC

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-10 : 16:11:14

Hi Peso,

Great, exactly what I was looking for, thanks again.

If I wanted to pass some RegEx expression into the query, is it a major ordeal ? The articles I am reading look like I have to create special tables etc ? I don't want to create a make work project as I am already swamped, but it would be nice to do something like at a simple RegEx expression into the LIKE clause.

Very much appreciated!!

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 16:13:39

What kind og regex's are you looking for?

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-10 : 16:25:24

Hi Peso,

on the like for the landing page, something like this would work amazing.

"/products/view_[0-9]+_[a-z0-9]+.html

Not sure if this is in an acceptable format?

Thanks again!!

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 02:38:52

It is.
The example you posted above will give all urls ending with

"/products/view[any single character][any single character 0 to 9]+[any single character][any single character a to z or 0 to 9]+.html"

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-11 : 07:24:19

Hi Peso,

Thats great news I can pass in this format

Problem is that it doesnt seem to be working for me. For example I run the query like so and get nothing back.

exec select_google_referrers_groupByQueryDate_OrderByQueryDate 300, '/products/view_[0-9]+_[a-zA-Z0-9]+.html'

I tried concatenating and a few other things, but I really have no idea how to mix the regEx parts with the normal string parts. Am I close ?

Thanks once agan, much appreciated

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 07:31:01

_ means ANY single character matches this position! Much like ? in DOS.

And what is the meaning of + (plus) signs?

'/products/view_5+_Q+.html' is a valid path with your current expression.

See http://msdn2.microsoft.com/en-us/library/ms179859.aspx

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-11 : 08:32:30

Hi Peso,

This is how I write it with my asp.net code, I guess things are a bit different. The + signs I think are just for concatenating, I will remove them from my SQL code. I am actually trying to literally match the "_" characters as they are characters I am looking for in the string. Hmm this might create some difficulties, reading the link now .. thanks for the tip :) RegEX has always flown over my head but starting to understand it a bit better now :)

I'm looking to make string matches for the following

/products/view_3000_asdf38r3jjj.html
/products/view_50_anytextswringHeremaybe33too.html
/products/view_334250_anynumberfirstpartTHENSTRINGSECONDPART.html

Thanks again!,
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-11 : 08:36:04

hmmm looks like I have to use an escape character I guess for the "_" .. trying to figure out how to integrate.. hmm

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:36:25

'/products/view[_][0-9]%[_][a-zA-Z0-9]%.html'

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-11 : 09:16:14