Converting string to unicode string in T-SQL

Hi,
We have stored proc name proc_test(str nvarchar(30)). So far this proc
has been invoked from a .NET application assuming that only English
character strings will be passed to it. The calls are like
proc_test('XYZ')
We now have a requirement for passing Chinese strings as well. Rather
than changing the calls throughout the application, we would like to
handle it in the stored procedure so that it treats the string as a
unicode string. Can we apply some function to the parameter to convert
it to unicode so that we don't have to call with an N prefixed to the
string?
Thanks,
YashHi
> unicode string. Can we apply some function to the parameter to convert
> it to unicode so that we don't have to call with an N prefixed to the
> string?
Do you mean to get an INTEGER of the string , then you have UNICODE function
, see in the BOL.
What's wrong with calling with an N prefixed to the string?
<yashgt@.gmail.com> wrote in message
news:1174896401.921135.170240@.b75g2000hsg.googlegroups.com...
> Hi,
> We have stored proc name proc_test(str nvarchar(30)). So far this proc
> has been invoked from a .NET application assuming that only English
> character strings will be passed to it. The calls are like
> proc_test('XYZ')
> We now have a requirement for passing Chinese strings as well. Rather
> than changing the calls throughout the application, we would like to
> handle it in the stored procedure so that it treats the string as a
> unicode string. Can we apply some function to the parameter to convert
> it to unicode so that we don't have to call with an N prefixed to the
> string?
> Thanks,
> Yash
>|||On Mar 26, 11:06 am, yas...@.gmail.com wrote:
> [...]
> We now have a requirement for passing Chinese strings as well. [...]
> Can we apply some function to the parameter to convert it to unicode
> so that we don't have to call with an N prefixed to the string?
No, you can't. If you don't prefix it with N, then the chinese
characters are lost in the implicit conversion to varchar, so you
cannot get them back (unless the varchar has a DBCS collation, which
would be if you have the default server collation on a Chinese_*
collation, but I don't think that would be a good idea).
Razvan|||<yashgt@.gmail.com> wrote in message
news:1174896401.921135.170240@.b75g2000hsg.googlegroups.com...
> Hi,
> We have stored proc name proc_test(str nvarchar(30)). So far this proc
> has been invoked from a .NET application assuming that only English
> character strings will be passed to it. The calls are like
> proc_test('XYZ')
You should go back and change your code to properly parameterize your
queries instead of concatenating the parameter values into strings,
VB5-style. Then you wouldn't have to worry about the N prefix. Or SQL
Injection.|||Are you building query strings in your .Net code by concatenating character
values? If so - why? Use parameters, or better yet use stored procedures.
ML
--
http://milambda.blogspot.com/

converting nvarchar to Minutes:Seconds in MS SQL?

I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error :The average aggregateoperation cannot take a nvarchar data type as an argument.

Help!!!! :(

You need to convert the minutes and seconds into some decimals which can be used in Avg function. Let's say you want to get Avg seconds, then you may try:

SELECT AvgSeconds= AVG(CASE WHEN CHARINDEX(':',[Calling Time])=0
THEN CONVERT(DECIMAL(4,2),[Calling Time])*60
WHEN CHARINDEX(':',[Calling Time])=1
THEN CONVERT(DECIMAL(4,2),RIGHT([Calling Time],LEN([Calling Time])-1))
ELSE CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],1,CHARINDEX(':',[Calling Time])-1))*60
+CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],CHARINDEX(':',[Calling Time])+1,LEN([Calling Time])))
END)
FROM test_CntTime

Converting Non-Unicode to Unicode

What is the best way to convert a large 24/7 database to Unicode? We have
various varchar and text fields that need to become nvarchar and ntext
respectively. I know I can alter the tables to do the varchars, but that
would have to be offline (looks like like about 2 hours per column on my
50 million record table). Text fields can't be converted with an alter so
they have to be copied to a new column. I was just wondering if anybody
has experience converting these.
Why not design another table with new datatypes and use DTS or BULK insert to import data from the old table.
Once data is propogated delete the old table and rename new to old to handle the application.
HTH
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Brad" wrote:

> What is the best way to convert a large 24/7 database to Unicode? We have
> various varchar and text fields that need to become nvarchar and ntext
> respectively. I know I can alter the tables to do the varchars, but that
> would have to be offline (looks like like about 2 hours per column on my
> 50 million record table). Text fields can't be converted with an alter so
> they have to be copied to a new column. I was just wondering if anybody
> has experience converting these.
>
|||In article <38E5F8AA-A720-4E27-A01F-9BB364263191@.microsoft.com>,
satyaskj@.yahoo.co.uk said...
> Why not design another table with new datatypes and use DTS or BULK insert to import data from the old table.
> Once data is propogated delete the old table and rename new to old to handle the application.
Because I need to do it as on-line as possible. If I do a copy I will
have to shut down the site before I begin and I know it will take quite a
while.

Converting Non-Unicode to Unicode

What is the best way to convert a large 24/7 database to Unicode? We have
various varchar and text fields that need to become nvarchar and ntext
respectively. I know I can alter the tables to do the varchars, but that
would have to be offline (looks like like about 2 hours per column on my
50 million record table). Text fields can't be converted with an alter so
they have to be copied to a new column. I was just wondering if anybody
has experience converting these.Why not design another table with new datatypes and use DTS or BULK insert t
o import data from the old table.
Once data is propogated delete the old table and rename new to old to handle
the application.
HTH
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Brad" wrote:

> What is the best way to convert a large 24/7 database to Unicode? We have
> various varchar and text fields that need to become nvarchar and ntext
> respectively. I know I can alter the tables to do the varchars, but that
> would have to be offline (looks like like about 2 hours per column on my
> 50 million record table). Text fields can't be converted with an alter so
> they have to be copied to a new column. I was just wondering if anybody
> has experience converting these.
>|||In article <38E5F8AA-A720-4E27-A01F-9BB364263191@.microsoft.com>,
satyaskj@.yahoo.co.uk said...
> Why not design another table with new datatypes and use DTS or BULK insert
to import data from the old table.
> Once data is propogated delete the old table and rename new to old to handle the a
pplication.
Because I need to do it as on-line as possible. If I do a copy I will
have to shut down the site before I begin and I know it will take quite a
while.

Converting Non-Unicode to Unicode

What is the best way to convert a large 24/7 database to Unicode? We have
various varchar and text fields that need to become nvarchar and ntext
respectively. I know I can alter the tables to do the varchars, but that
would have to be offline (looks like like about 2 hours per column on my
50 million record table). Text fields can't be converted with an alter so
they have to be copied to a new column. I was just wondering if anybody
has experience converting these.In article <38E5F8AA-A720-4E27-A01F-9BB364263191@.microsoft.com>,
satyaskj@.yahoo.co.uk said...
> Why not design another table with new datatypes and use DTS or BULK insert to import data from the old table.
> Once data is propogated delete the old table and rename new to old to handle the application.
Because I need to do it as on-line as possible. If I do a copy I will
have to shut down the site before I begin and I know it will take quite a
while.