CODEWITHSUNDEEP
sql-server
user1300588
user1300588

Reputation: 63

How to check the string whether it was arabic language or not in SQL Server 2008

How can I check if the string is arabic or not based one that I have to update the my language ID column as English or Arabic 

 IF(PATINDEX('%[^أ-ي]%', RTRIM(@STRING))=0)
        BEGIN
              SET @RETURNVALUE='A'
        END
 ELSE IF(PATINDEX('%[^A-Za-z]%', RTRIM(@STRING))=0)
        BEGIN
              SET @RETURNVALUE='E'
        END

If suppose name starts with S and follows arabic name it was throwing null

EX: set @STRING= N'العربيةs'

kindly help to sort out this issue ?

Upvotes: 2

Views: 6466

Answers (1)

devio
devio

Reputation: 37215

The IF statement you use effectively says:

If the string does not contain a non-arabic character, set result to 'A'

Otherwise, if the string does not contain a non-latin character, set result to 'E'

If you have strings containing both scripts, arabic AND latin, the result value is not set.

Remove the negation in the regex, like this:

declare @string nvarchar(50), @returnvalue varchar (50)

set @string = N'العربيةs'
set @returnvalue = ''

IF(PATINDEX(N'%[أ-ي]%', RTRIM(@string ))>0)
    BEGIN
      SET @returnvalue= @returnvalue + 'A'
    END
IF(PATINDEX(N'%[A-Za-z]%', RTRIM(@string ))>0)
    BEGIN
      SET @returnvalue= @returnvalue + 'E'
    END

 select @string, @returnvalue

returns

العربيةs    AE

update after 1st comment

IF(PATINDEX(N'%[أ-ي]%', RTRIM(@string ))>0)
    BEGIN
      SET @returnvalue= 'A'
    END
ELSE IF(PATINDEX(N'%[A-Za-z]%', RTRIM(@string ))>0)
    BEGIN
      SET @returnvalue= 'E'
    END

RETURN @returnvalue

Upvotes: 5

Related Questions