convert nvarchar to int

  • 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

  • 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 (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.

  • dr_csharp (2/16/2010)


    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 (2/16/2010)


    dr_csharp (2/16/2010)


    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 🙂

  • dr_csharp (2/16/2010)


    1-as you know i asked this question and no one has appropriate answer after 2 days.

    You know that if you don't get an 'appropriate answer' after 3 days, you're entitled to a full refund? :laugh:

    Never mind, I'm taking a look at this now, I'll post back in a bit.

    Paul

  • dr_csharp (2/16/2010)


    1-as you know i asked this question and no one has appropriate answer after 2 days.

    I think the lack of detail in your original question didn't help. This is your original post (in its entirety)

    dr_csharp (2/14/2010)


    dear friends

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

    thanks

    I think you'll agree there isn't an awful lot of detail on your exact problem.

  • Here we go then...

    -- Switch to the tempdb database

    USE tempdb;

    GO

    -- Drop any objects created by this script if they are still around from a previous run

    IF OBJECT_ID(N'dbo.SampleData', N'U') IS NOT NULL DROP TABLE dbo.SampleData;

    IF OBJECT_ID(N'dbo.ConvertedData', N'U') IS NOT NULL DROP TABLE dbo.ConvertedData;

    IF OBJECT_ID(N'dbo.TriggerTest', N'U') IS NOT NULL DROP TABLE dbo.TriggerTest;

    IF OBJECT_ID(N'dbo.ToPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.ToPersian;

    IF OBJECT_ID(N'dbo.FromPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.FromPersian;

    GO

    -- Inline table-valued function (ITVF) to convert latin numbers to persian

    -- SCHEMABINDING used to force the function to be evaluated for determinism

    -- ITVFs are much faster than scalar functions, and can operate on sets of data

    CREATE FUNCTION dbo.ToPersian (@Input AS NVARCHAR(40))

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    -- Nested replace to convert each of the ten possible characters

    SELECT converted =

    CONVERT(NVARCHAR(40),

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    -- Use a binary collation for comparisons, for speed

    @Input COLLATE LATIN1_GENERAL_BIN,

    N'9', N'?'),

    N'8', N'?'),

    N'7', N'?'),

    N'6', N'?'),

    N'5', N'?'),

    N'4', N'?'),

    N'3', N'?'),

    N'2', N'?'),

    N'1', N'?'),

    N'0', N'?'));

    GO

    -- Inline table-valued function to convert persian numbers to latin

    -- SCHEMABINDING used to force the function to be evaluated for determinism

    -- ITVFs are much faster than scalar functions, and can operate on sets of data

    CREATE FUNCTION dbo.FromPersian (@Input NVARCHAR(40))

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    -- Nested replace to convert each of the ten possible characters

    SELECT converted =

    CONVERT(NVARCHAR(40),

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    -- Use a binary collation for comparisons, for speed

    @Input COLLATE LATIN1_GENERAL_BIN,

    N'?', N'9'),

    N'?', N'8'),

    N'?', N'7'),

    N'?', N'6'),

    N'?', N'5'),

    N'?', N'4'),

    N'?', N'3'),

    N'?', N'2'),

    N'?', N'1'),

    N'?', N'0'));

    GO

    -- Test 1: Convert a single value from latin to persian

    SELECT latin_to_persian_test = converted

    FROM dbo.ToPersian(N'123456789.01')

    GO

    -- Test 2: Convert the result of test 1 back to latin

    SELECT round_trip_conversion_test = FP.converted

    FROM dbo.ToPersian(N'123456789.01') TP

    CROSS

    APPLY dbo.FromPersian(TP.converted) FP

    GO

    -- Test 3: Show that the two inline functions have been checked and are deterministic and precise

    SELECT function_name = N'dbo.ToPersian',

    is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ToPersian', N'IF'), 'IsDeterministic'),

    is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ToPersian', N'IF'), 'IsPrecise'),

    is_sytem_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ToPersian', N'IF'), 'IsSystemVerified')

    UNION ALL

    SELECT N'dbo.FromPersian',

    OBJECTPROPERTYEX(OBJECT_ID(N'dbo.FromPersian', N'IF'), 'IsDeterministic'),

    OBJECTPROPERTYEX(OBJECT_ID(N'dbo.FromPersian', N'IF'), 'IsPrecise'),

    OBJECTPROPERTYEX(OBJECT_ID(N'dbo.FromPersian', N'IF'), 'IsSystemVerified');

    GO

    -- Create a table to hold some sample data in persian format

    CREATE TABLE dbo.SampleData

    (

    latin_format INTEGER NOT NULL,

    persian_format NVARCHAR(40) NOT NULL,

    );

    GO

    -- Create a table to hold the results of converting the sample data to latin

    CREATE TABLE dbo.ConvertedData

    (

    converted INTEGER NOT NULL,

    )

    GO

    -- Create a table to test using a trigger

    CREATE TABLE dbo.TriggerTest

    (

    data NVARCHAR(40) NOT NULL,

    );

    GO

    -- Test 4:

    -- Add the numbers 1 to 32,767 to the sample data table

    -- The numbers will be converted from latin to persian format

    WITH Numbers

    AS (

    -- This generates the numbers 1 to 32,767

    SELECT TOP (32767)

    n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))

    FROM master.sys.columns C1,

    master.sys.columns C2

    )

    INSERT dbo.SampleData WITH (TABLOCK)

    (latin_format, persian_format)

    SELECT Numbers.n, CA.converted

    FROM Numbers

    CROSS

    APPLY -- Convert latin to persian

    dbo.ToPersian (n) CA;

    GO

    -- Show a sample of the converted data

    SELECT TOP (10)

    test_4 = SD.persian_format

    FROM dbo.SampleData SD;

    GO

    -- Test 5:

    -- Convert the 32,767 persian numbers in dbo.SampleData to latin

    -- and store in the dbo.ConvertedData table

    INSERT dbo.ConvertedData WITH (TABLOCK)

    (converted)

    SELECT CA.converted

    FROM dbo.SampleData SD

    CROSS

    APPLY -- Convert persian to latin

    dbo.FromPersian (SD.persian_format) CA;

    GO

    -- Show a sample of the converted data

    SELECT TOP (10)

    test_5 = CD.converted

    FROM dbo.ConvertedData CD;

    GO

    -- Create a trigger on the dbo.TriggerTest table

    -- to convert persian INSERTs to latin format

    -- as part of the insert

    CREATE TRIGGER trg_TriggerTest

    ON dbo.TriggerTest

    INSTEAD OF INSERT

    AS

    BEGIN

    SET NOCOUNT ON; -- Suppress x row(s) affected messages

    SET ROWCOUNT 0; -- Restore ROWCOUNT

    -- If no rows were INSERTed, exit now

    IF NOT EXISTS (SELECT * FROM inserted) RETURN;

    -- Convert the persian data to latin

    -- and store the result in the table

    INSERT dbo.TriggerTest

    SELECT CA.converted

    FROM inserted INS

    CROSS

    APPLY dbo.FromPersian (INS.data) CA;

    END;

    GO

    -- Test 6:

    -- Test the trigger by inserting the 32,767 rows of persian data from the dbo.SampleData table

    INSERT dbo.TriggerTest WITH (TABLOCK)

    (data)

    SELECT persian_format

    FROM dbo.SampleData;

    GO

    -- Show that the persian data was converted to latin by the trigger

    SELECT test_6 = TT.data

    FROM dbo.TriggerTest TT WITH (TABLOCK);

    GO

    -- Tidy up (comment this out to leave the objects in place for inspection)

    IF OBJECT_ID(N'dbo.SampleData', N'U') IS NOT NULL DROP TABLE dbo.SampleData;

    IF OBJECT_ID(N'dbo.ConvertedData', N'U') IS NOT NULL DROP TABLE dbo.ConvertedData;

    IF OBJECT_ID(N'dbo.TriggerTest', N'U') IS NOT NULL DROP TABLE dbo.TriggerTest;

    IF OBJECT_ID(N'dbo.ToPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.ToPersian;

    IF OBJECT_ID(N'dbo.FromPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.FromPersian;

    -- End of script

  • Nice solution Paul.

    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

  • CirquedeSQLeil (2/17/2010)


    Nice solution Paul.

    Indeed, very thorough. I think you've scared off the OP though 🙂

  • nigel. (2/18/2010)


    CirquedeSQLeil (2/17/2010)


    Nice solution Paul.

    Indeed, very thorough. I think you've scared off the OP though 🙂

    I hope not - but even if that is the case, I would hope that someone else might read it with interest.

    In fact the algorithm I use in my functions is not terribly efficient in some scenarios - other solutions to this sort of character-replacement problem exist - but it is definitely better than a WHILE loop inside a scalar T-SQL function.

    My personal preference would normally be to write it as a CLR function - either scalar or streaming TVF, depending on the requirement. CLR scalar functions are much quicker to call than T-SQL scalar functions, and a streaming TVF can be optimal on larger inputs sets.

    The inline T-SQL function is very efficient from a plug-in to the query plan point of view, but all those nested REPLACEs would drag performance down on longer strings. Anyway, I digress.

    I thought I should keep this example fairly simple - I think a whole load of C# would have scared off more than the OP! Though, judging from his forum name, he might have preferred it...?

    Paul

Viewing 11 posts - 16 through 25 (of 25 total)

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