Function name is ambigious in SQL server

Question

Based on this question, I've created a function and added it to my database. Now I want to use that function as a calculated column:

-- Based on https://mcmap.net/q/118137/-how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server (work by Even Mien)
CREATE FUNCTION [dbo].[StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END
GO

-- Table
CREATE TABLE [dbo].[Trailer]
(
    [ID] INT NOT NULL PRIMARY KEY IDENTITY,
    [ID_Hauler] INT NULL,
    [RegistrationNumber] NCHAR(9) NOT NULL,
    [RegistrationNumberSimplified] AS [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),
    [MaxLoad] FLOAT NULL,

    CONSTRAINT [FK_Hauler_Trailer] FOREIGN KEY ([ID_Hauler]) REFERENCES [Hauler]([ID]),
    CONSTRAINT [UC_RegistrationNumber] UNIQUE ([RegistrationNumberSimplified])
)

However, the line where I reference the function throws an error:

"Berechnete Spalte: [dbo].[Trailer].[RegistrationNumberSimplified]" enthält einen nicht aufgelösten Verweis auf ein Objekt. Entweder ist das Objekt nicht vorhanden, oder der Verweis ist mehrdeutig, da er auf die folgenden Objekte verweisen könnte: [dbo].[StripCharacters] oder [dbo].[Trailer].[dbo]::[StripCharacters].

"Either the object doesn't exist, or the reference is ambigious, because it could mean either [dbo].[StripCharacter] or [dbo].[Trailer].[dbo]::[StripCharacter]."

I've also tried to let the server guess the namespace of the function. In that case, the database throws an error:

(57,1): SQL72014: .Net SqlClient Data Provider: Meldung 195, Ebene 15, Status 10, Zeile 11 'StripCharacters' is not a recognized built-in function name.

What's the problem here?

I also found this question, but the answer doesn't help me, because I don't use database references.

Deanery

StripCharacters exists in two different schamas the first under dbo and the second under [dbo].[Trailer].[dbo] – Olwena

@AmiraBedhiafi But why? The error occurs in a Visual Studio database project and the only place, where I define that function is in an SQL file with the exact same code as in Amy's post in the linked question. – Arrowood

Try to drop the [dbo].StripCharacters and run your query to know more about the issue – Olwena

Amy's function is called fn_StripCharacters, not StripCharacters – Achernar

Even so, if you fix that, you'll get this error, because it's an nvarchar(MAX). Change that and you get this error (which I'd already warned you about in your other question). – Achernar

@AmiraBedhiafi We are not even on the database yet. The error comes from Visual Studio itself. Hence, I can't publish the changes to the database and therefore can't drop anything, because it doesn't even exist yet. – Arrowood

@Larnu I've changed the name, because we generally don't use prefixes. Other than that, the function's code is identical. – Arrowood

Are you sure? If you have got the name wrong you get the error you describe: db<>fiddle – Achernar

@Larnu Yes. Even, if I change the function name to fn_, I get the following error: SQL71501: "Berechnete Spalte: [dbo].[Trailer].[RegistrationNumberSimplified]" enthält einen nicht aufgelösten Verweis auf ein Objekt. Entweder ist das Objekt nicht vorhanden, oder der Verweis ist mehrdeutig, da er auf die folgenden Objekte verweisen könnte: [dbo].[fn_StripCharacters] oder [dbo].[Trailer].[dbo]::[fn_StripCharacters]. – Arrowood

The problem you're trying to fix it fruitless anyway though, @AndréReichelt, for the reasons I stated above. Even if you do figure out why this is happened (which I can't replicate other than ensuring I have an invalid object name) you'll have a problem when you try to create the CONSTRAINT as the value is not deterministic. You can't fix that with the function you have – Achernar

@Larnu I see your point. But the same error still appears, when I change my code to [RegistrationNumberSimplified] NCHAR(9) NOT NULL DEFAULT [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),. – Arrowood

Can you post the DDL for dbo.StripCharacters? – Reeducate

@AlanBurstein I've edited my question – Arrowood

@Larnu - this is easy to fix. dbfiddle.uk/… – Amitie

Interesting that a WHILE can be deterministic, for using a tally is not, @MartinSmith :/ – Achernar

@MartinSmith @Larnu I realized that already. The function WITH SCHEMABINDING is considered to be deterministic. SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[StripCharacters]'), 'IsDeterministic') returns 1. – Arrowood

@Larnu Would you like to try, if your approach from yesterday is considered to be deterministic as well? – Arrowood

I did try @AndréReichelt . It's likely because I was using an inline table-value function as part of the process to strip the characters out. I didn't delve too far in in honesty. – Achernar

@Larnu That could be the reason. At first glance, I thought that reinitializing the table for every call was a bottleneck. – Arrowood

Answer 1

What a strange problem! After copying the source code of the function into a newly created scalar function file in my database project, and then deleting the original file and renaming the newly created one afterwards, it finally works. Because I didn't change any code at all, I guess, that this was some pretty weird bug of Visual Studio.

Deanery

Answer 2

The problem you are having is that the function is returning a value too large to be used as a key column. The max would be NVARCHAR(850) or VARCHAR(1700). This would work:

-- Based on https://mcmap.net/q/118137/-how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server (work by Even Mien)
ALTER FUNCTION [dbo].[StripCharacters]
(
    @String NVARCHAR(850), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(850) WITH SCHEMABINDING 
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String
END
GO

-- Table
CREATE TABLE [dbo].[Trailer]
(
    [ID] INT NOT NULL PRIMARY KEY IDENTITY,
    [ID_Hauler] INT NULL,
    [RegistrationNumber] NCHAR(9) NOT NULL,
    [RegistrationNumberSimplified] AS [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),
    [MaxLoad] FLOAT NULL

    CONSTRAINT [FK_Hauler_Trailer] FOREIGN KEY ([ID_Hauler]) REFERENCES [Hauler]([ID]),
    CONSTRAINT [UC_RegistrationNumber] UNIQUE ([RegistrationNumberSimplified])
);

Reeducate

Unfortunately, that doesn't help. Even, if I change the return type to CHAR(10), the error doesn't go away. – Arrowood