I am having some difficulty getting a case expression to work. I need to run a case expression on a field that has many different characters and conditions. I first need to strip the field of all non-numeric values, and then set a value based on the value of the field, I have been able to write a case expression that does this, but it is extremely slow. This works:

CASE
WHEN (SELECT CAST(CAST((SELECT SUBSTRING(fieldtoreplace, Number, 1) FROM master..spt_values WHERE Type='p' AND Number <= LEN(fieldtoreplace) AND SUBSTRING(fieldtoreplace, Number, 1) LIKE '[0-9]' FOR XML Path('')) AS xml) AS varchar(255))) BETWEEN 1 and 10 THEN 'MIN'
WHEN (SELECT CAST(CAST((SELECT SUBSTRING(fieldtoreplace, Number, 1) FROM master..spt_values WHERE Type='p' AND Number <= LEN(fieldtoreplace) AND SUBSTRING(fieldtoreplace, Number, 1) LIKE '[0-9]' FOR XML Path('')) AS xml) AS varchar(255))) BETWEEN 11 and 25 THEN 'MID'
WHEN (SELECT CAST(CAST((SELECT SUBSTRING(fieldtoreplace, Number, 1) FROM master..spt_values WHERE Type='p' AND Number <= LEN(fieldtoreplace) AND SUBSTRING(fieldtoreplace, Number, 1) LIKE '[0-9]' FOR XML Path('')) AS xml) AS varchar(255))) = 26 THEN 'MAX'
ELSE 'UNKNOWN'
END

However, I was hoping to be able to do something more like below where it only runs the transformation once instead of for each line of the case statement.

CASE (SELECT CAST(CAST((SELECT SUBSTRING(fieldtoreplace, Number, 1) FROM master..spt_values WHERE Type='p' AND Number <= LEN(fieldtoreplace) AND SUBSTRING(fieldtoreplace, Number, 1) LIKE '[0-9]' FOR XML Path('')) AS xml) AS varchar(255)))
WHEN BETWEEN 1 and 10 THEN 'MIN'
WHEN BETWEEN 11 and 25 THEN 'MID'
WHEN 26 THEN 'MAX'
ELSE 'UNKNOWN'
END

The actual expression is 41 different cases with various between and equals conditions.

Does anyone have a solution for doing this so that the transformation only has to be run once? the table it is running against is extremely large.

Output for both should be:

9    MIN
5    MIN
26   MAX
26   MAX
25   MID
44   UNKNOWN