how to convert persian number to int

Question

There is a nvarchar(100) column named value, when users insert into this column I need to check this code below in a trigger:

if exists
(
    select *
    from inserted i
    where isnumeric(value)=0
)
begin
    rollback transaction
    raiserror('when productType is numeric, You have to insert numeric character',18,1)
    return
end

but in application interface numbers inserted in persian, so always isnumeric(value)=0.

For example I need to if user insert ۴۵ in interface in my trigger value shown as 45.

So far I use CAST,CONVERT and collate Persian_100_CI_AI but I couldn't get any result.

Thanks.

Answer 1

Which version of SQL Server? v2017+ offers a new function TRANSLATE.

Might be, there is a more elegant way, but a pragmatic one is this:

DECLARE @PersianNumber NVARCHAR(100)=N'۴۵';
SELECT CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
           (@PersianNumber,N'۰',N'0'),N'۱',N'1'),N'۲',N'2'),N'۳',N'3'),N'۴',N'4')
                          ,N'۵',N'5'),N'۶',N'6'),N'۷',N'7'),N'۸',N'8'),N'۹',N'9') AS INT);