Home Need help removing functions from CASE WHEN
 I have a situation where I have created script to select data in our company's environment. In doing so, I decided to use functions for some pattern matching and stripping of characters in a CASE WHEN. However, one of our clients doesn't want to let us put their data in our local environment, so I now have the requirement of massaging the script to be able to run on their environment--essentially meaning I need to remove the functions, and I am having trouble thinking about how I need to move stuff around to do so. An example of the function call would be: SELECT .... CASE WHEN Prp = 'Key Cabinet' AND SerialNumber IS NOT NULL AND dbo.fnRemoveNonNumericCharacters(SerialNumber) <> '' THEN dbo.fnRemoveNonNumericCharacters(SerialNumber) .... INTO #EmpProperty FROM ....  Where Prp is a column that contains the property type and SerialNumber is a column that contains a serial number, but also some other random garbage because data entry was sloppy. The function definition is: WHILE PATINDEX('%[^0-9]%', @strText) > 0 BEGIN SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '') END RETURN @strText  where @strText is the SerialNumber I am passing in. I may be stuck in analysis paralysis because I just can't figure out a good way to do this. I don't need a full on solution per-say, perhaps just point me in a direction you know will work. Let me know if you would like some sample DDL/DML to mess around with stuff. Example 'SerialNumber' values: CA100 (Trash bins), T110, 101B. There are also a bunch of other types of values such as all text or all numbers, but we are filtering those out. The current patterning matching is good enough.