convert nvarchar to int

Question

convert nvarchar to int

dr_csharp

SSCommitted

Points: 1765
More actions
February 13, 2010 at 11:19 pm

#386310

dear friends
how can i convert a field with nvarchar type to int?
thanks

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms187928.aspx

You can do it - but data can be truncated, only partially displayed, or an error returned because the result is too short to display. Use the link provided to see how to do it.

Also, you can only do it if the data stored in the NVarChar field is a number.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

CirquedeSQLeil (2/13/2010)
http://msdn.microsoft.com/en-us/library/ms187928.aspx
You can do it - but data can be truncated, only partially displayed, or an error returned because the result is too short to display. Use the link provided to see how to do it.
Also, you can only do it if the data stored in the NVarChar field is a number.

data stored in nvarchar field is number but in UTF-8, when im trying "CAST(myField AS INT)" it drop error :

Conversion failed when converting the nvarchar value '?' to data type int.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You may need to check your collation settings. Not the same issue - but similar and with their solution

http://stackoverflow.com/questions/119477/sql-server-2005-xml-stored-proc-unicode-to-ascii-exception-0xc00ce508

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dr_csharp SSCommitted Points: 1765 More actions · Answer 4

CirquedeSQLeil (2/14/2010)
You may need to check your collation settings. Not the same issue - but similar and with their solution
http://stackoverflow.com/questions/119477/sql-server-2005-xml-stored-proc-unicode-to-ascii-exception-0xc00ce508

i create new database with SQL_Latin1_General_CP1_CI_AS collasion, but still same error !

please help me

LutzM SSC Guru Points: 107049 More actions · Answer 5

What is the result of the following query?

SELECT * FROM myTable WHERE myField like '%[^0-9]%'

It will return all rows that might cause problems when converting to INT.

Maybe this will help you to identify the rows that cause the error.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

"data stored in nvarchar field is number but in UTF-8"

This is not possible as national characters in SQL Server are stored in UCS-2 (Universal Character Set 2 byte) and code page 65001 (UTF-8 encoding ) is not supported. SQL Server does not support translating UTF-8 into UCS-2 meaning that if a national character string is passed to SQL Server, it is intepreted as UCS-2 and there is no method of indicating that the encoding is actually UTF-8

Data in UTF-8 encoding needs to translated to UCS-2 before being placed in a SQL Server table.

SQL = Scarcely Qualifies as a Language

dr_csharp SSCommitted Points: 1765 More actions · Answer 7

Carl Federl (2/14/2010)
"data stored in nvarchar field is number but in UTF-8"
This is not possible as national characters in SQL Server are stored in UCS-2 (Universal Character Set 2 byte) and code page 65001 (UTF-8 encoding ) is not supported. SQL Server does not support translating UTF-8 into UCS-2 meaning that if a national character string is passed to SQL Server, it is intepreted as UCS-2 and there is no method of indicating that the encoding is actually UTF-8
Data in UTF-8 encoding needs to translated to UCS-2 before being placed in a SQL Server table.

yse, exactly. data stored in nvarchar field is number but in UTF-8.

what can i do know ?

Paul White SSC Guru Points: 150467 More actions · Answer 8

http://msdn.microsoft.com/en-us/library/ms160893(SQL.90).aspx

(UTF8 String User-Defined Type)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

naveena 78933 SSC Enthusiast Points: 101 More actions · Answer 9

naveena 78933

SSC Enthusiast

Points: 101

February 16, 2010 at 5:14 am

#1119183

select cast(column name as int) from table name

dr_csharp SSCommitted Points: 1765 More actions · Answer 10

eventually i forced to write a function.

i put here for friends like use :

CREATE FUNCTION [dbo].[CONVERTOR](

@string nvarchar(10)

)

returns nvarchar(10)

AS BEGIN

declare @converted nvarchar(10),

@charnvarchar(10),

@counterint,

@lengthint,

@unicodeint,

@asciiint

set @counter = 1

set @converted=N'';

set @length=LEN(@string)

while(@counter <= @length)

begin

set @char=substring(@string,@counter,1)

set @unicode= UNICODE(@char)

set @char=@unicode-1776

set @converted=@converted+@char;

set @counter = @counter + 1

end

return @converted

END

select dbo.CONVERTOR(myFiled) FROM myTable

this scalar function convert unicode to ascii 🙂

thanks all anyway

Paul White SSC Guru Points: 150467 More actions · Answer 11

I hope everyone that reads this thread appreciates the limitations of that function.

Yikes!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Limitations and performance implications.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Carl Federl One Orange Chip Points: 25384 More actions · Answer 13

Are you certain that your data in in UTF-8 ? If all of the data is actually ASCII, a sub-set of UTF-8, then you can just use a varchar datatype.

SQL = Scarcely Qualifies as a Language

dr_csharp SSCommitted Points: 1765 More actions · Answer 14

Paul White (2/16/2010)
I hope everyone that reads this thread appreciates the limitations of that function.
Yikes!

i dont know what do u mean by limitation, cause this function has one goal and that is converting a field from UTF-8 to Asccii, for example convert '???' to '462'

but why don't you help for more efficiency and rid limitations ?