Replace numbers with arabic numbers in SQL select query

Introduction

Sometimes we need to display numeric data in crystal reports. By default, crystal report doesn't support the feature to convert english integer value to arabic numbers.

Though we can script a function to do so but it will be limited only for crystal reports.

Think of retrieving the data from the database in arabic numbers format...

This approach will help both in backend level and application level. Here I'm writing a SQL function to replace numbers with arabic numbers.

This task can be accomplished by scripting below mentioned SQL function.

CREATE FUNCTION [dbo].[udf_replaceIntegersWithArabicNumbers]
    (@str NVARCHAR(1000))
    RETURNS NVARCHAR(2000)
AS
BEGIN

    DECLARE @i INT = 1
    WHILE @i<=LEN(@str)

    BEGIN
        DECLARE @val NVARCHAR(1)
        SET @val = SUBSTRING(@str, @i, 1)
        IF(@val) >= '0' and (@val) <= '9'
        
        BEGIN
            DECLARE @newchar NVARCHAR(1)
            SET @newchar = CASE(@val)
                WHEN 1 THEN N'۱'
                WHEN 2 THEN N'۲'
                WHEN 3 THEN N'۳'
                WHEN 4 THEN N'٤'
                WHEN 5 THEN N'۵'
                WHEN 6 THEN N'٦'
                WHEN 7 THEN N'۷'
                WHEN 8 THEN N'۸'
                WHEN 9 THEN N'۹'
                WHEN 0 THEN N'۰'
        END
        SET @str = REPLACE(@str, @val, @newchar)
    END        
    SET @i+=1
END
RETURN @str
END

How to use this function

SELECT dbo.udf_replaceIntegersWithArabicNumbers('hello 123 world456')

Output