SQL Clone
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-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16522 Visits: 11355
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-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33776 Visits: 18560
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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3408 Visits: 4350
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-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16522 Visits: 11355
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16522 Visits: 11355
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-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16522 Visits: 11355
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-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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