Mary February 2016

"N" before text in sql server

I have a function

create Function [dbo].[persianNum]
(
@persionstr nvarchar(255)
)
Returns nvarchar(255)
 AS
Begin
Declare @out nvarchar(255)
-- code
 return @out
END

and I call this : " select dbo.persianNum (N'۰۸/۰۲/۲۰۱۶ ۱۰:۱۳:۵۵') " this line Worked correctly.

now I wanted to call this in my trigger '۰۸/۰۲/۲۰۱۶ ۱۰:۱۳:۵۵' is in @myInput

 select dbo.persianNum (@myinput)

but I need " N " before my input How put it before the @myinput

ALTER Function [dbo].[persianNum]
(
@persionstr nvarchar(255)
)
Returns nvarchar(255)
--۰۸/۰۲/۲۰۱۶ ۱۰:۱۴:۴۴ ۰۸/۰۲/۲۰۱۶ ۱۰:۱۳:۵۵
AS
Begin
Declare @out nvarchar(255)
Declare @ss nvarchar(255)
Declare @persianTemp nvarchar(255)
Declare @len int

set @len = LEN(@persionstr)
   set @persianTemp= @persionstr
  set @out=''
while @len>0


Begin
set @len=@len-1;
 set @ss = SUBSTRING(@persianTemp, 1, 1)
set @persianTemp=SUBSTRING(@persianTemp, 2, @len)
set @out=@out+
CASE @ss
WHEN N'۰' THEN N'0'
WHEN N'۱' THEN N'1'
WHEN N'۲' THEN N'2'
WHEN N'۳' THEN N'3'
WHEN N'۴' THEN N'4'
WHEN N'۵' THEN N'5'
WHEN N'۶' THEN N'6'
WHEN N'۷' THEN N'7'
WHEN N'۸' THEN N'8'
WHEN N'۹' THEN N'9'

ELSE @ss
END


END       
     return @out
END

this is my complete function

Answers


Jaco February 2016

The N is used to cast as string as NVARCHAR, once a string is already an NVARCHAR you don't need to cast it again. You need to define @myinput as NVARCHAR otherwise your Persian text will not be encoded correctly.


destination-data February 2016

You don't put the N before @myinput. Instead place it where you set the value of your variable.

Exampe

SET @myinput = N'۰۸/۰۲/۲۰۱۶ ۱۰:۱۳:۵۵';

Post Status

Asked in February 2016
Viewed 2,565 times
Voted 9
Answered 2 times

Search




Leave an answer