CODEWITHSUNDEEP
sql-server-2008
Mayur_Vartak
Mayur_Vartak

Reputation: 161

I want sum of column having datatype nvarchar in sql server 2008

Here i have table test having column id with datatype nvarchar contains values like १२३४ २३६८० ४५९०

so i want to do sum of these values. Basically i want to do manipulation with above numbers. please do the needful asap.

Upvotes: 1

Views: 1162

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Something like this perhaps.

declare @T table(id nvarchar(10))

insert into @T values
(N'१२३४'),
(N'२३६८०'), 
(N'४५९०')

select sum(cast(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
         id , 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))
from @T

Result:

(No column name)
29504

Update:

To be able to reuse this you can create a scalar valued function:

create function dbo.DevanagariToInt(@Value nvarchar(10)) returns int
begin
  return cast(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
           @Value
         , 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)
end

And use it like this:

select sum(dbo.DevanagariToInt(ID))
from @T

Upvotes: 1

Related Questions