CODEWITHSUNDEEP
sqlsql-serverfunction
Dizzy49
Dizzy49

Reputation: 1528

SQL Function Stuck and Will Not Return Value

I have a fairly simple function that takes a string, checks for values and replaces them and returns the resulting string after all replacements. No loops or anything.

However, when I run it, it just sits there like it's stuck in a loop and will not return any value.

Here is the function in question:

ALTER FUNCTION [dbo].[fn_DeNormalizeCo] (@oldStr varchar(255))
    RETURNS varchar(255)
BEGIN
    DECLARE 
        @newStr varchar(255)

    SET @newStr = LTRIM(RTRIM(@oldStr))

    -- Tab  \t
    IF (@newStr LIKE '%'+CHAR(9)+'%')
        SET @newStr = REPLACE(@newStr, CHAR(9), '')
    
    -- Linefeed \n
    IF (@newStr LIKE '%'+CHAR(10)+'%')
        SET @newStr = REPLACE(@newStr, CHAR(10), '')
    
    -- Carriage Return \r
    IF (@newStr LIKE '%'+CHAR(13)+'%')
        SET @newStr = REPLACE(@newStr, CHAR(13), '')

    -- °
    IF (@newStr LIKE '%'+CHAR(176)+'%')
        SET @newStr = REPLACE(@newStr, CHAR(176), '')
    
    IF (@newStr LIKE '%#%')
        SET @newStr = REPLACE(@newStr, '#', '')

    IF (@newStr LIKE '%:%')
        SET @newStr = REPLACE(@newStr, ':', '')

    IF (@newStr LIKE '%;%')
        SET @newStr = REPLACE(@newStr, ';', '')

    IF (@newStr LIKE '%@%')
        SET @newStr = REPLACE(@newStr, '@', '')

    IF (@newStr LIKE '%[%]%')
        SET @newStr = REPLACE(@newStr, '%', '')

    IF (@newStr LIKE '%*%')
        SET @newStr = REPLACE(@newStr, '*', '')

    IF (@newStr LIKE '%&%')
        SET @newStr = REPLACE(@newStr, '&', '')

    IF (@newStr LIKE '%!%')
        SET @newStr = REPLACE(@newStr, '!', '')

    IF (@newStr LIKE '%$%')
        SET @newStr = REPLACE(@newStr, '$', '')

    IF (@newStr LIKE '%"%')
        SET @newStr = REPLACE(@newStr, '"', '')

    IF (@newStr LIKE '%''%')
        SET @newStr = REPLACE(@newStr, '''', '')

    

    IF (@newStr LIKE '%,INC.%')
        SET @newStr = REPLACE(@newStr, ',INC.', '')

    IF (@newStr LIKE '%,INC%')
        SET @newStr = REPLACE(@newStr, ',INC', '')

    IF (@newStr LIKE '%, INC.%')
        SET @newStr = REPLACE(@newStr, ', INC.', '')

    IF (@newStr LIKE '%, INC%')
        SET @newStr = REPLACE(@newStr, ', INC', '')

    IF (@newStr LIKE '% INC.')
        SET @newStr = REPLACE(@newStr, ' INC.', '')

    IF (@newStr LIKE '% INC')
        SET @newStr = REPLACE(@newStr, ' INC', '')


    IF (@newStr LIKE '%,LLC.%')
        SET @newStr = REPLACE(@newStr, ',LLC.', '')

    IF (@newStr LIKE '%,LLC%')
        SET @newStr = REPLACE(@newStr, ',LLC', '')

    IF (@newStr LIKE '%, LLC.%')
        SET @newStr = REPLACE(@newStr, ', LLC.', '')

    IF (@newStr LIKE '%, LLC%')
        SET @newStr = REPLACE(@newStr, ', LLC', '')

    IF (@newStr LIKE '%LLC.')
        SET @newStr = REPLACE(@newStr, ' LLC.', '')

    IF (@newStr LIKE '% LLC')
        SET @newStr = REPLACE(@newStr, ' LLC', '')



    IF (@newStr LIKE '%,CO.%')
        SET @newStr = REPLACE(@newStr, ',CO.', '')

    IF (@newStr LIKE '%,CO%')
        SET @newStr = REPLACE(@newStr, ',CO', '')

    IF (@newStr LIKE '%, CO.%')
        SET @newStr = REPLACE(@newStr, ', CO.', '')

    IF (@newStr LIKE '%, CO%')
        SET @newStr = REPLACE(@newStr, ', CO', '')

    IF (@newStr LIKE '% CO.%') 
        SET @newStr = REPLACE(@newStr, ' CO.', '')

    IF (@newStr LIKE '% CO%') 
        SET @newStr = REPLACE(@newStr, ' CO', '')



    IF (@newStr LIKE '%,CORP.')
        SET @newStr = REPLACE(@newStr, ',CORP.', '')

    IF (@newStr LIKE '%,CORP')
        SET @newStr = REPLACE(@newStr, ',CORP', '')

    IF (@newStr LIKE '%, CORP.')

        SET @newStr = REPLACE(@newStr, ', CORP.', '')

    IF (@newStr LIKE '%, CORP')
        SET @newStr = REPLACE(@newStr, ', CORP', '')

    IF (@newStr LIKE '% CORP.')
        SET @newStr = REPLACE(@newStr, ' CORP.', '')

    IF (@newStr LIKE '% CORP')
        SET @newStr = REPLACE(@newStr, ' CORP', '')



    IF (@newStr LIKE '%,MFG.')
        SET @newStr = REPLACE(@newStr, ',MFG.', '')

    IF (@newStr LIKE '%,MFG')
        SET @newStr = REPLACE(@newStr, ',MFG', '')

    IF (@newStr LIKE '%, MFG.')
        SET @newStr = REPLACE(@newStr, ', MFG.', '')

    IF (@newStr LIKE '%, MFG')
        SET @newStr = REPLACE(@newStr, ', MFG', '')

    IF (@newStr LIKE '% MFG.')
        SET @newStr = REPLACE(@newStr, ' MFG.', '')

    IF (@newStr LIKE '% MFG')
        SET @newStr = REPLACE(@newStr, ' MFG', '')



    IF (@newStr LIKE '%,INCORPORATED.%')
        SET @newStr = REPLACE(@newStr, ',INCORPORATED.', '')

    IF (@newStr LIKE '%,INCORPORATED%')
        SET @newStr = REPLACE(@newStr, ',INCORPORATED', '')

    IF (@newStr LIKE '%, INCORPORATED.%')
        SET @newStr = REPLACE(@newStr, ', INCORPORATED.', '')

    IF (@newStr LIKE '%, INCORPORATED%')
        SET @newStr = REPLACE(@newStr, ', INCORPORATED', '')

    IF (@newStr LIKE '% INCORPORATED.')
        SET @newStr = REPLACE(@newStr, 'INCORPORATED.', '')

    IF (@newStr LIKE '% INCORPORATED')
        SET @newStr = REPLACE(@newStr, 'INCORPORATED', '')


    RETURN @newStr
END

I've tried a simple call with: SELECT [ULTRA].[dbo].[fn_DeNormalizeCo]('Noname LLC')

Just to rule out the fact that I'm calling a function from another database, I also tried it from the [ULTRA] database with SELECT [dbo].[fn_DeNormalizeCo]('Noname LLC') with the same result of being stuck.

I'm really at a loss here, I swear I was using this function fine before I had to wipe and reload my PC.

I'm using:

Upvotes: 1

Views: 279

Answers (1)

allmhuran
allmhuran

Reputation: 4474

Since you're on SQL Server 2019, it might be worth trying

alter function [dbo].[fn_DeNormalizeCo] (@oldStr varchar(255)) 
returns varchar(255) 
with inline = OFF

.. if you have the database compatibility level at 150 and inlining enabled.

Scalar inlining is still known to be buggy.

Upvotes: 2

Related Questions