dimanche 8 mai 2016

How can I pass sql query to User Defined Function

I have a function as follows

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

DECLARE @i INT
SET @i = 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=@i+1
END
RETURN @str
END

I want to update the value of a table column to arabic numerals.i.e. I have a column and I want to update the column value as follows:

column value
------------
 1
 2
33


column value New
----------------
     ۱                    
     ۲
    ۳۳ 

That's why I have written the following query. But it throws an error. Can I do what I want in this way. Is there any other way to do that.

update topicinfo 
set topicfullcodearabic = (select dbo.udf_replaceIntegersWithArabicNumbers (select topiccode from topicinfo t1 where topicinfo.topicid=t1.topicid
))
where topicid in (select topicid 
                  from topicinfo t2 
                  where topicinfo.topicid = t2.topicid)

This query causes these errors:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

Aucun commentaire:

Enregistrer un commentaire