Abbreviation in where clause

Question

Treat abbreviation like Sta. equals to Santa, or Ma. equals to Maria or Ma.

SELECT * FROM Table1 A WITH(NOLOCK) WHERE A.Name LIKE '%ma%'

This query is for Maria, but the problem is it also included a name contains marie

Case 1: User input ma the result would be maria, ma, and ma. and vice versa.

Case 2: User Inputs Maria the outputs would be Ma. Teresa or Maria Teresa or Ma Teresa and same goes on sta scenario.

Case 3: User input sta the result would be santa, sta. and sta and vice versa.

Case 4: User input normal name (Jose) without abbreviation the result would be like Jose

Any idea on this problem? or what is the best approach for this problem?

Thank you.

Answer 1

This is function to check if the search contains ma, ma.....

CREATE FUNCTION [dbo].[StringContains]
(
-- Add the parameters for the function here
@String1 VARCHAR(MAX),
@String2 VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
-- Declare the return variable here
DECLARE @Result BIT = 0

DECLARE @IsContain INT = 0

DECLARE @Equivalents TABLE
(
    ID INT IDENTITY(1,1),
    Text1 VARCHAR(50),
    Text2 VARCHAR(50)
)
INSERT INTO @Equivalents (Text1, Text2)
VALUES ('ma. ','maria'),
('ma. ','ma '),
('ma ','maria'),
('sta. ','santa'),
('sta. ','sta '),
('sta ','santa')

DECLARE @ID INT = 1
DECLARE @TOTAL INT = (SELECT COUNT(1) FROM @Equivalents)
WHILE @ID <= @TOTAL
BEGIN
    DECLARE
        @Temp1 VARCHAR(MAX) = @String1,
        @Temp2 VARCHAR(MAX) = @String2,
        @Text1 VARCHAR(50),
        @Text2 VARCHAR(50)
    SELECT TOP 1
        @Text1 = Text1,
        @Text2 = Text2
    FROM @Equivalents
    WHERE ID = @ID

    IF @Temp1 LIKE '%'+@Text1+'%' AND @Temp2 LIKE '%'+@Text2+'%'
    BEGIN
        SET @String2 = REPLACE(@Temp2,@Text2,@Text1)
    END
    ELSE IF @Temp1 LIKE '%'+@Text2+'%' AND @Temp2 LIKE '%'+@Text1+'%'
    BEGIN
        SET @String2 = REPLACE(@Temp2,@Text1,@Text2)
    END

    IF @String1 LIKE '%'+@String2+'%'
    BEGIN
        SET @IsContain += 1
    END

    SET @ID += 1
END

SET @Result = (CASE WHEN @IsContain > 0 THEN 1 ELSE 0 END)
RETURN @Result
END

And this is the query.

DECLARE @Text VARCHAR(MAX) = 'maria'
SELECT TOP 100 FullName,* FROM dbo.Table1 WITH(NOLOCK)
WHERE dbo.StringContains(FullName,@Text) = 1