Design with data -how to get the longest/shortest product/category name from Ucommerce

There's little more frustrating that finalising a store and loading in the product data only to find it doesn't look as beautiful as the design. This is usually because the designer has chosen a product title from the site which looks good in the design rather than testing the extremes (too long/short).

Here's a little script which will help if you're working with existing Ucommerce data, it lists the longest and shortest titles for the products, categories and discount codes. If you wanted to, you could add the product descriptions to this as well.

It will output something like this:

DataTypeRowNumLengthTypeNameLengthCategory1ShortestEquine6Category1LongestTack Room Essentials20Category Description1ShortestEquine6Category Description1LongestTack Room Essentials20Discount Code1ShortestTEST4Discount Code1LongestEXAMPLE7Product1ShortestTest4Product1LongestHorse Worm Count Kit & Pinworm Test - commercial yards54Product Description1ShortestTest4Product Description1LongestHorse worm count and pinworm test kit for commercial yards59

If you would like the top/bottom x for each, just change "WHERE RowNum<=1" to the number you would like.

SELECT d.DataType
	 , d.RowNum
	 , d.LengthType
	 , d.Name
	 , d.Length
FROM 
(
	SELECT 'Product' AS DataType,'Shortest' AS LengthType, P.Name, LEN(P.Name) AS [Length], RowNum = ROW_NUMBER() OVER (ORDER BY LEN(P.Name) ASC) FROM uCommerce_Product p WHERE p.DisplayOnSite=1 UNION ALL
	SELECT 'Product','Longest', P.Name, LEN(P.Name), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(P.Name) DESC) FROM uCommerce_Product p WHERE p.DisplayOnSite=1 UNION ALL
	SELECT 'Product Description','Shortest', pd.DisplayName, LEN(Pd.DisplayName), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(Pd.DisplayName) ASC) FROM uCommerce_ProductDescription pd JOIN uCommerce_Product p ON pd.ProductId = p.ProductId WHERE p.DisplayOnSite=1 AND LEN(pd.DisplayName)>1 UNION ALL
	SELECT 'Product Description','Longest', pd.DisplayName, LEN(Pd.DisplayName), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(Pd.DisplayName) DESC) FROM uCommerce_ProductDescription pd JOIN uCommerce_Product p ON pd.ProductId = p.ProductId WHERE p.DisplayOnSite=1 AND LEN(pd.DisplayName)>1 UNION ALL
	SELECT 'Category','Shortest', c.Name, LEN(c.Name), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(c.Name) ASC) FROM uCommerce_Category c WHERE c.DisplayOnSite=1 UNION ALL
	SELECT 'Category','Longest', c.Name, LEN(c.Name), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(c.Name) DESC) FROM uCommerce_Category c WHERE c.DisplayOnSite=1 UNION ALL
	SELECT 'Category Description','Shortest', c.Name, LEN(cd.DisplayName), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(c.Name) ASC) FROM uCommerce_Category c JOIN uCommerce_CategoryDescription cd ON c.CategoryId = cd.CategoryId  WHERE c.DisplayOnSite=1 AND LEN(cd.DisplayName)>1 UNION ALL
	SELECT 'Category Description','Longest', c.Name, LEN(cd.DisplayName), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(c.Name) DESC) FROM uCommerce_Category c JOIN uCommerce_CategoryDescription cd ON c.CategoryId = cd.CategoryId WHERE c.DisplayOnSite=1 AND LEN(cd.DisplayName)>1 UNION ALL
	SELECT 'Discount Code','Shortest', c.Code, LEN(c.Code), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(c.Code) ASC) FROM uCommerce_VoucherCode c WHERE c.NumberUsed < c.MaxUses UNION ALL
	SELECT 'Discount Code','Longest', c.Code, LEN(c.Code), RowNum = ROW_NUMBER() OVER (ORDER BY LEN(c.Code) DESC) FROM uCommerce_VoucherCode c WHERE c.NumberUsed > c.MaxUses
	
) d
WHERE RowNum <= 1
ORDER BY DataType, LengthType DESC, RowNum ASC