The Site Doctor Blog

Footprints in the snow of a warped mind

< Back to Blog

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
Author: Tim on

Liked this post? Got a suggestion? Leave a comment