Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Wednesday, September 16, 2015

SQL Server - Determining the language of a name: Arabic English or Multilingual.


As part of Data Cleansing project, I struggled with one specific case while developing custom algorithms to cleanse, detect and smartly handle duplicates in master files. The case was primarily related to fields with mixed language. 

The original legacy system has fields for the name in which; sometimes, both Arabic and Language characters are recorded. Theoretically speaking, there must be a very simple way out of this in SQL Server, which is not supposed to be time-consuming neither a reinvention of the wheel. 

There was someone in the Stack Overflow community talking about something similar but not precisely the exact thing, he was proposing a way to determine the language of the field by using the Patter Index function in SQL server, his way is quite simple and to the point, which looks for any of the alphabetical characters within the passed string, and return the position of this character. If the return value is larger than 0, it means that any of the alphabetical characters was found (either English or Arabic). Although, that would never be comprehensive in my case as some fields may contain both characters. The function is illustrated below:


CREATE   FUNCTION [dbo].[Fn_CheckName] ( @string NVARCHAR(MAX) )
RETURNS NVARCHAR(100)
BEGIN
    DECLARE @Value NVARCHAR(100)
    IF ( PATINDEX(N'%[أ-ي]%', RTRIM(@string)) > 0 )
        BEGIN
            SET @Value = 'A'
        END
    ELSE
        IF ( PATINDEX(N'%[A-Za-z]%', RTRIM(@string)) > 0 )
            BEGIN
                SET @Value = 'E'
            END

    RETURN @Value
END

GO

Now, the alternative of checking the pattern within the name which could be misinterpreted and processed, is to consider the first and last characters of the string, and compare the result with the original result retrieved by the function above. As follows:


SELECT  ( SELECT    dbo.Fn_CheckName(NAME)
        ) AS Language ,
        LEN(NAME) Length ,
        ( SELECT    dbo.Fn_CheckName(SUBSTRING(NAME, 1, 1))
        ) AS F_Character_Language ,
              ( SELECT    dbo.Fn_CheckName(SUBSTRING(NAME, 2, 1))
        ) Second_Character_Checkname,
        ( SELECT    dbo.Fn_CheckName(SUBSTRING(NAME, LEN(name), 1)))   
AS L_Character_Language ,     
        name
FROM    [dbo].[namelist]


Here is the overall criteria when breaking down the name into characters and check the language of the first and last character to determine which is an Arabic, English or Multilingual field.


 
Process Visualization



Best Regards,
Mahmoud M. AlSaadi


No comments:

Post a Comment