Need help removing functions from CASE WHEN

Question

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.

Answer 1

So I think you mean you can't use a function... so, perhaps:

declare @table table (SomeCol varchar(4000))
insert into @table values
('1 ab2cdefghijk3lmnopqr4stuvwxyz5 6 !7@#$8%^&9*()-10_=11+[]{}12\|;:13></14? 15'),
('CA100 (Trash bins), T110, 101B')

;with cte as (
    select top (100) 
    N=row_number() over (order by @@spid) from sys.all_columns),

Final as (
    select SomeCol, Col
    from @table
        cross apply (
            select (select X + ''
            from (select N, substring(SomeCol, N, 1) X 
                  from cte 
                  where N<=datalength(SomeCol)) [1]
            where X between '0' and '9'
            order by N
            for xml path(''))
        ) Z (Col)
    where Z.Col is not NULL
)

select 
    SomeCol
    ,cast(Col as varchar) CleanCol  --change this to BIGINT if it isn't too large
from Final