Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

convert nvarchar to int Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 5:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 5:23 AM
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
Post #866002
Posted Tuesday, February 16, 2010 7:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
I hope everyone that reads this thread appreciates the limitations of that function.
Yikes!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #866100
Posted Tuesday, February 16, 2010 10:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 18,060, Visits: 16,092
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
Post #866252
Posted Tuesday, February 16, 2010 3:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 2,281, Visits: 4,241
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
Post #866566
Posted Tuesday, February 16, 2010 9:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 5:23 AM
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 ?
Post #866720
Posted Tuesday, February 16, 2010 10:22 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #866743
Posted Tuesday, February 16, 2010 10:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #866747
Posted Tuesday, February 16, 2010 10:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 5:23 AM
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.
Post #866750
Posted Tuesday, February 16, 2010 11:06 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #866756
Posted Tuesday, February 16, 2010 11:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 8, 2010 5:23 AM
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
Post #866763
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse