Reputation: 10981

sql split field by comma delimiter

Given the following.

Products_Joined.METATAG_Keywords AS Keyword,

This returns this for "keyword"

shoe,sneaker,boot,slipper

I am tying to split this into separate fields as so.

keyword1    shoe
keyword2    sneaker
keyword3    boot
keyword4    slipper

Something like a Javascript split function on comma delimiter.

Unfortunately this is how this field is stored in the database.

EDIT: Although I do not think it matters much I have provided the full sql query, perhaps someone can tell which version of SQL this is.

SELECT
'home-and-garden-products > home decorations > rugs' AS Category,
SUBSTRING(Products_Joined.ProductName, 6,LEN(Products_Joined.ProductName)) AS [stripHTML-Title],
'Config_FullStoreURL' + 'ProductDetails.asp?ProductCode=' + Products_Joined.IsChildOfProductCode AS Link,
Products_Joined.ProductCode AS SKU,
CAST(IsNull(Products_Joined.SalePrice,Products_Joined.ProductPrice) AS VARCHAR(30)) AS Price,
Products_Joined.ProductManufacturer AS Brand,
Products_Joined.UPC_code AS UPC,
'Config_FullStoreURLConfig_ProductPhotosFolder/' + IsNull(Products_Joined.IsChildOfProductCode,Products_Joined.ProductCode) + '-2T.jpg' AS Image,
Products_Joined.ProductDescription AS [stripHTML-DESCRIPTION],
Products_Joined.ProductManufacturer AS Manufacturer,
Products_Joined.vendor_partno AS [Mfr part number],
'0' AS [Shipping Cost],
Products_Joined.Google_Color AS Color,
Products_Joined.Google_Material AS Material,
Products_Joined.ProductWeight AS [Shipping Weight], 
Products_Joined.Google_Size AS Size,
Products_Joined.METATAG_Keywords AS Keyword,
Products_Joined.ProductWeight AS Weight
FROM Products_Joined WITH (NOLOCK)
WHERE (Products_Joined.ProductPrice > 0) 
AND (Products_Joined.IsChildOfProductCode IS NOT NULL) 
ORDER BY Products_Joined.ProductCode

Upvotes: 0