SQL scripts to check and migrate invalid decimal formatting in Article Attribute Values

for MASTER and SUPPLIER database

-- This script can be used after migration of a pim database older than version 5 in order to correct old formatted article attribute values.
--
-- It searches for invalid formatted decimal values in ArticleAttributeValue.Value and ArticleAttributeValue.ValueMax
-- which have only one comma and only digits in it, and replaces the comma with a dot
-- f.e.: 
--   - "1,2" becomes "1.2"
--   - "10000,23" becomes "10000.23"
--
-- Limitations:
--   Old values with thousands separator (f.e. "1.000,2") are not migrated. Also values with characters other than digits and "," (f.e. "1,2 mm") are not migrated.
-- Execute the following statements once on the MASTER database and once on the SUPPLIER database.
-- 1. search for invalid values (here you have the ability to recheck, if these values should be migrated)
SELECT ar.Identifier, aav.ID, aav.Value, aav.ValueMax
FROM  ArticleRevision ar
INNER JOIN ArticleAttribute aa ON aa.ArticleRevisionID = ar.ID
INNER JOIN ArticleAttributeValue aav ON aa.ID = aav.ArticleAttributeID
WHERE aa.DataTypeID in (2, 3, 7) AND 
     (((len(aav.Value) - len(replace(aav.Value, ',', ''))) = 1 AND aav.Value not like '%[^0-9,]%') OR 
	  ((len(aav.ValueMax) - len(replace(aav.ValueMax, ',', ''))) = 1 AND aav.ValueMax not like '%[^0-9,]%'))
-- 2. correct invalid values in ArticleAttributeValue.Value
UPDATE ArticleAttributeValue set Value = replace (Value, ',', '.')
WHERE ID in (
SELECT aav.ID
FROM ArticleAttribute aa 
INNER JOIN ArticleAttributeValue aav ON aa.ID = aav.ArticleAttributeID
WHERE aa.DataTypeID in (2, 3, 7)
AND (len(aav.Value) - len(replace(aav.Value, ',', ''))) = 1 
AND aav.Value not like '%[^0-9,]%'
)
-- 2. correct invalid values in ArticleAttributeValue.ValueMax
UPDATE ArticleAttributeValue set ValueMax = replace (ValueMax, ',', '.')
WHERE ID in (
SELECT aav.ID
FROM ArticleAttribute aa 
INNER JOIN ArticleAttributeValue aav ON aa.ID = aav.ArticleAttributeID
WHERE aa.DataTypeID in (2, 3, 7)
AND (len(aav.ValueMax) - len(replace(aav.ValueMax, ',', ''))) = 1 
AND aav.ValueMax not like '%[^0-9,]%'
)

for MAIN database

-- This script can be used after migration of a pim database older than version 5 in order to correct old formatted structure group attribute values.
--
-- It searches for invalid formatted decimal values in StructureGroupAttributeVal.Value and StructureGroupAttributeVal.ValueMax
-- which have only one comma and only digits in it, and replaces the comma with a dot
-- f.e.: 
--   - "1,2" becomes "1.2"
--   - "10000,23" becomes "10000.23"
--
-- Limitations:
--   Old values with thousands separator (f.e. "1.000,2") are not migrated. Also values with characters other than digits and "," (f.e. "1,2 mm") are not migrated.
-- Execute the following statements on the MAIN database.
-- 1. search for invalid values (here you have the ability to recheck, if these values should be migrated)
SELECT sgav.Identifier, sgav.ID, sgav.Value, sgav.ValueMax
FROM StructureGroupAttribute sga
INNER JOIN StructureGroupAttributeVal sgav ON sgav.StructureGroupAttributeID = sga.ID
WHERE DataTypeID in (2, 3, 7) AND 
      (((len(sgav.Value) - len(replace(sgav.Value, ',', ''))) = 1 AND sgav.Value not like '%[^0-9,]%') OR
	   ((len(sgav.ValueMax) - len(replace(sgav.ValueMax, ',', ''))) = 1 AND sgav.ValueMax not like '%[^0-9,]%'))
-- 2. correct invalid values in StructureGroupAttributeVal.Value
UPDATE StructureGroupAttributeVal set Value = replace (Value, ',', '.')
WHERE ID in (
SELECT sgav.ID
FROM StructureGroupAttribute sga
INNER JOIN StructureGroupAttributeVal sgav ON sgav.StructureGroupAttributeID = sga.ID
WHERE DataTypeID in (2, 3, 7)
AND (len(sgav.Value) - len(replace(sgav.Value, ',', ''))) = 1 
AND sgav.Value not like '%[^0-9,]%'
)
-- 2. correct invalid values in StructureGroupAttributeVal.ValueMax
UPDATE StructureGroupAttributeVal set ValueMax = replace (ValueMax, ',', '.')
WHERE ID in (
SELECT sgav.ID
FROM StructureGroupAttribute sga
INNER JOIN StructureGroupAttributeVal sgav ON sgav.StructureGroupAttributeID = sga.ID
WHERE DataTypeID in (2, 3, 7)
AND (len(sgav.ValueMax) - len(replace(sgav.ValueMax, ',', ''))) = 1 
AND sgav.ValueMax not like '%[^0-9,]%'
)