When you need to perform a SQL select and do substitution at the same time (for example if you would like to remove nulls from the selection) you can use the CASE command. Here is an example.
ChristopherSELECT
RTRIM(LTRIM(AssemblyNumber)) AS AssNum,
RTRIM(LTRIM(PartNumber)) AS PrtNum,
CASE WHEN ItemNotes IS NULL THEN '' ELSE RTRIM(LTRIM(ItemNotes)) END AS ItemNotes,
CASE WHEN PATINDEX('%[^0-9]%', ISNULL(ItemQuantity, '*')) > 0 THEN 1 ELSE RTRIM(LTRIM(ItemQuantity)) END AS ItemQuantity,
CASE WHEN ItemHeight IS NULL THEN '' ELSE RTRIM(LTRIM(ItemHeight)) END AS ItemHeight,
CASE WHEN ItemWidth IS NULL THEN '' ELSE RTRIM(LTRIM(ItemWidth)) END AS ItemWidth,
CASE WHEN ItemLength IS NULL THEN '' ELSE RTRIM(LTRIM(ItemLength)) END AS ItemLength,
CASE WHEN ItemMaterial IS NULL THEN '' ELSE RTRIM(LTRIM(ItemMaterial)) END AS ItemMaterial,
CASE WHEN ShapeIH IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIH)) END AS ShapeIH,
CASE WHEN ShapeIW IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIW)) END AS ShapeIW,
CASE WHEN ShapeIL IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIL)) END AS ShapeIL
FROM zzzImportManualBOMs
ORDER BY
RTRIM(LTRIM(REPLACE(REPLACE(AssemblyNumber, '.', ''), '_', ''))),
RTRIM(LTRIM(REPLACE(REPLACE(PartNumber, '.', ''), '_', '')))
