Home Need help removing functions from CASE WHEN
Reply: 0

Need help removing functions from CASE WHEN

user1916
1#
user1916 Published in July 17, 2018, 7:56 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307407 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO