SQL Saturday Sponsor..
May 19, 2012 Leave a Comment
I just signed up as a SQL Saturday sponsor. Here is the logo that I put together to use for the sponsorship link. Good thing my SQL skills are much stronger than my Photoshop skills.
SQL Lunch UK
May 8, 2012 Leave a Comment
Today I started the day early with a great presentation for the SQL Lunch UK group on Common Table Expressions.
For those who attended the presentation here is the Zip file with the Powerpoint and the SQL code from the demo. Feel free to download it and learn from it.
Here are a few links to some of the other CTE examples used in the demo.
- Using a CTE in a Function to Split Up a Query String
- Using a CTE to Split a String Into Rows
- Can we use CTEs use in SSRS?
- CTE Hierarchy compared to the alternative
- Calculating Factorials with a Recursive CTE
- CTE – With An Insert Statement
- Fibonacci Sequence
- Generating a Tree Path with a CTE
- CTE Query Performance
- Multiple CTE’s in a single Query
- Recursive CTE’s
- Common Table Expressions Page
- A Fun Thanksgiving Day Query.
- Common Table Expressions
Good luck to everyone who is new to common table expressions. Special thanks to Dave Ballantyne to inviting me to participate in this event.
SQL Lunch UK presentation next week.
May 2, 2012 Leave a Comment
On May 8th at 4:30am (Lunchtime UK) I will be presenting my Unleashing Common Table Expressions in an online webcast type presentation.
I am looking forward to it. I have tuned it up a bit since the last time I presented it at SQL Saturday in Vancouver Canada.
3 Sessions submitted to SQL Saturday in Salt Lake City
April 22, 2012 Leave a Comment
I am planning on visiting Salt Lake City for SQL Saturday in October.
Using a CTE in a Function to Split Up a Query String
April 9, 2012 1 Comment
The question came up as to how do I parse a query string using TSQL. So here you go, using a common table expression, and basing this on a similar function to what I put together yesterday for split.
CREATE FUNCTION dbo.SplitQueryString (@s varchar(8000))
RETURNS table
AS
RETURN (
WITH splitter_cte AS (
SELECT CHARINDEX('&', @s) as pos, 0 as lastPos
UNION ALL
SELECT CHARINDEX('&', @s, pos + 1), pos
FROM splitter_cte
WHERE pos > 0
),
pair_cte AS (
SELECT chunk,
CHARINDEX('=', chunk) as pos
FROM (
SELECT SUBSTRING(@s, lastPos + 1,
case when pos = 0 then 80000
else pos - lastPos -1 end) as chunk
FROM splitter_cte) as t1
)
SELECT substring(chunk, 0, pos) as keyName,
substring(chunk, pos+1, 8000) as keyValue
FROM pair_cte
)
GO
declare @queryString varchar(2048)
set @queryString = 'foo=bar&temp=baz&key=value';
SELECT *
FROM dbo.SplitQueryString(@queryString)
OPTION(MAXRECURSION 0);
which produces this output.
A quick and easy way to parse a query string in TSQL using a CTE.
Using a CTE to Split a String Into Rows
April 8, 2012 Leave a Comment
One of the common questions that comes up in my CTE class/presentation asks can you use the CTE inside of a function or stored procedure. The answer is yes, but today I came across another question, how can I split a string (varchar) into rows. I did some searching but didn’t find quite what I was looking for. I did fine one CTE example, but I didn’t like the way it worked, so I decided to create my own Split function, using a recursive CTE to implement the split function.
GO
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
WITH splitter_cte AS (
SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos
UNION ALL
SELECT CHARINDEX(@sep, @s, pos + 1), pos
FROM splitter_cte
WHERE pos > 0
)
SELECT SUBSTRING(@s, lastPos + 1,
case when pos = 0 then 80000
else pos - lastPos -1 end) as chunk
FROM splitter_cte
)
GO
SELECT *
FROM dbo.Split(' ', 'the quick brown dog jumped over the lazy fox')
OPTION(MAXRECURSION 0);
which produced the following output.
chunk --------------------- the quick brown dog jumped over the lazy fox (9 row(s) affected)
There are programming tasks that can be accomplished easily with a Recursive Common Table Expression.
