Sometimes I bring information to a couple of my other WordPress blogs via RSS feed. It’s a nice feature, allowing you to create several posts at once without manual entry. Unfortunately if RSS feed is broken or improperly formatted it can result in blank posts imported into the blog.
I was looking for a WordPress plugin that would allow me to mass-delete empty posts, but apparently none exist. You can delete posts based on date, tags, category, but not the content. Fortunately if you have access to phpMyAdmin of your MySQL installation – there is a solution. Continue reading 'WordPress MU: Delete Empty Posts'»
Imagine after running a query like this:
SELECT ContactName, Country FROM Customers ORDER BY Country
on Northwind database and getting following result:
ContactName Country
Patricio Simpson Argentina
Yvonne Moncada Argentina
Sergio Gutiérrez Argentina
Georg Pipps Austria
Roland Mendel Austria
Catherine Dewey Belgium
Pascale Cartrain Belgium
Anabela Domingues Brazil
Paula Parente Brazil
Bernardo Batista Brazil
Lúcia Carvalho Brazil
Janete Limeira Brazil
Aria Cruz Brazil
André Fonseca Brazil
Mario Pontes Brazil
Pedro Afonso Brazil
Elizabeth Lincoln Canada
Jean Fresnière Canada
Yoshi Tannamuri Canada
...
You’re asked to retreive only the 2nd and the 5th group of contacts. How do you do it? Continue reading 'Select specific groups using DENSE_RANK'»
T-SQL dialect of SQL doesn’t have aggregate functions for strings, but there is an easy workaround using magic of XML.
Consider Employees table of the Northwind database. When I run following query:
SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName
I get following result:
Country FirstName
UK Anne
UK Michael
UK Robert
UK Steven
USA Andrew
USA Janet
USA Laura
USA Margaret
USA Nancy
Now I want to combine first names into comma separated strings grouped by country. Continue reading 'T-SQL String Aggregate in SQL Server'»
Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable
Dim dv As New DataView(i_dSourceTable)
'getting distinct values for group column
Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn})
'adding column for the row count
dtGroup.Columns.Add("Count", GetType(Integer))
'looping thru distinct values for the group, counting
For Each dr As DataRow In dtGroup.Rows
dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
Next
'returning grouped/counted result
Return dtGroup
End Function
The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s “ToTable” method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s “Compute” method – Count in this case, but it can easily be replaced with other aggregates.
IsNumeric(data) is a widely used function in SQL Server programming. If passed in data is a number – it returns 1, otherwise 0. Supposedly. There’re several cases when it returns 1 even though the data is not numeric. Some of those cases are: dollar sign “$”, minus sign “-”, space, tab. If you try to call Cast or Convert function on the data after that to convert data to number - they will throw an error
An excellent article at SQL Hacks suggest adding “e0″ to the data when passing it to IsNumeric function. What it does essentualy is making the number to be in scientific format, e.g 12.34 becomes 12.34e0 (or 12.34 * 1). Which still remains a number. So IsNumeric(12.34e0) returns 1 while IsNumeric($e0) returns 0.
Which works great until actual number in scientific format is passed. If you try to test for example 1.234e5, IsNumeric correctly returns 1, but by adding “e0″ as article suggest IsNumeric(1.2345e5e0) returns 0.
To work around this limitation I simple test for an “e” within passed data. If it exist, I am calling IsNumeric as usual, if it does not – I am adding “e0″ to the tested data:
CREATE FUNCTION IsRealNumber (@i_sValueToTest nvarchar(250))
/* This function tests if passed in value a real number */
RETURNS int
AS
BEGIN
DECLARE @iResult int
– if there is ‘E’ in the value – do a standard IsNumeric test
IF CHARINDEX(‘E’, UPPER(@i_sValueToTest))<> 0
SET @iResult = IsNumeric(@i_sValueToTest)
– Otherwise do a modified IsNumeric test by adding ‘E0′ at the end
ELSE
SET @iResult = IsNumeric(@i_sValueToTest + ‘E0′)
RETURN @iResult
END
Comments (1) 
Themocracy