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.
Christopher
SELECT
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, '.', ''), '_', '')))
I have a small problem with many of the web-based, database-driven apps that I make for my company. The problem is that the users want to have a drop-down box with all the possible user names (or person’s names) when they need to select a persons’ name, which is reasonable until there are multiple selections like this on one page. Currently there are around 1000 users at my company, and we have some pages with up 40 select boxes for user names. That’s over 40,000 lines of code just to display the user select boxes, which means the page size would be well in excess of 4 megabytes.
This page has been moved …