Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert nvarchar to int


convert nvarchar to int

Author
Message
dr_csharp
dr_csharp
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 189
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),
         @char      nvarchar(10),
         @counter   int,
         @length      int,
         @unicode   int,
         @ascii      int                  

   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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
I hope everyone that reads this thread appreciates the limitations of that function.
Yikes!



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21071 Visits: 18259
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

Carl Federl
Carl Federl
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 4349
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
dr_csharp
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 189
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 ?
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
dr_csharp (2/16/2010)
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 ?

The lack of sample data and a clear definition of what you want to achieve makes it hard to help.
Don't take my criticism of your function so personally.
Unicode/UTF-8 is not a specialism of mine, but I am happy to contribute if I can, and if you make it easier to do so.
If it would help you, I can list the aspects of the function that I consider to be 'limitations'.
Otherwise, post some sample data and a specification, and you'll get some suggestions.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Right. I think I have worked out what you are trying to do. Correct me on the following where I am wrong:

You have data stored in a database which is numeric, but written in Urdu/Persian.
The data is stored in an NVARCHAR or NCHAR field.
You require a method to convert this data from the Urdu representation to the Hindu-Arabic numerals like so:

۹ = 9
۸ = 8
۷ = 7
۶ = 6
۵ = 5
۴ = 4
۳ = 3
۲ = 2
۱ = 1
۰ = 0

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
dr_csharp
dr_csharp
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 189
Paul White (2/16/2010)
Right. I think I have worked out what you are trying to do. Correct me on the following where I am wrong:

You have data stored in a database which is numeric, but written in Urdu/Persian.
The data is stored in an NVARCHAR or NCHAR field.
You require a method to convert this data from the Urdu representation to the Hindu-Arabic numerals like so:

۹ = 9
۸ = 8
۷ = 7
۶ = 6
۵ = 5
۴ = 4
۳ = 3
۲ = 2
۱ = 1
۰ = 0

Paul


Yes Paul, data are numeric stored in NVarchar fields in persian. and the function i did, is going to convert any number to latin format.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
dr_csharp (2/16/2010)
[quote]Yes Paul, data are numeric stored in NVarchar fields in persian. and the function i did, is going to convert any number to latin format.

Any other details you'd like to share? There's nothing worse than doing a whole heap of work, only for someone to turn around and say, "Oh yeah, I probably should have mentioned..."

Some sample input data and expected output would really help a lot.

Knowing the size of the data and how it is used and how important performance is to you would also help.

Thanks

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
dr_csharp
dr_csharp
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 189
Paul White (2/16/2010)
dr_csharp (2/16/2010)
[quote]Yes Paul, data are numeric stored in NVarchar fields in persian. and the function i did, is going to convert any number to latin format.

Any other details you'd like to share? There's nothing worse than doing a whole heap of work, only for someone to turn around and say, "Oh yeah, I probably should have mentioned..."

Some sample input data and expected output would really help a lot.

Knowing the size of the data and how it is used and how important performance is to you would also help.

Thanks

Paul

1-as you know i asked this question and no one has appropriate answer after 2 days.
2-i converted all my past data, and change this function to a 'after insert trigger' to do conversion online.
3-nothing specific for sample, just seems a record have some normal data and 3 other fields ( Comment,Vote,Click ) in Persian representation.

i appreciate if you know better way or any alter that improve performance :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search