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:

 

DataType	RowNum	LengthType	Name	Length
Category	1	Shortest	Equine	6
Category	1	Longest	Tack Room Essentials	20
Category Description	1	Shortest	Equine	6
Category Description	1	Longest	Tack Room Essentials	20
Discount Code	1	Shortest	TEST	4
Discount Code	1	Longest	EXAMPLE	7
Product	1	Shortest	Test	4
Product	1	Longest	Horse Worm Count Kit & Pinworm Test - commercial yards	54
Product Description	1	Shortest	Test	4
Product Description	1	Longest	Horse worm count and pinworm test kit for commercial yards	59

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