Post reply

Inserting symbols in a field

  • July 29, 2007 at 6:26 pm

    #174000

    Hi All,

    I am trying to insert symbols in a field of a table.

    I am using the datatype nvarchar for the field.

    However when i tried to insert a statement with the symbol pi, it comes out as following:

    (A) 100? cm2    (B) 140? cm2    (C) 200? cm2

    All the pi symbol are converted to ?.

    Can ne 1 tell me how i can store such strings in the field.

    Thanks

  • July 29, 2007 at 6:51 pm

    #723208

    To calculate PI you need float data type to get correct results and to store the symbol in  Nvarchar you need to make the column collation Greek, you are getting character conversion.  The first link is SQL Server math functions and the other two links is SQL Server collation, any column you need to store all the Greek math functions you need Greek collation and Nvarchar.  Hope this helps.

    http://msdn2.microsoft.com/en-us/library/ms177516.aspx

     

    Collation links.

    http://msdn2.microsoft.com/en-us/library/ms179886.aspx

    http://msdn2.microsoft.com/en-us/library/ms180175.aspx

     

    Kind regards,
    Gift Peddie

  • July 31, 2007 at 1:52 am

    #723490

    Hi Ahbi,

    One possible problem is the way you insert the data into the table. If you have a nvarchar field, then you should insert the constant text data using the N prefix, so something like:

    insert into alma2 values (N'?p')

    In this case the unicode data is preserved, and not converted to ASCII first. Inserting the text as above works fine. If you do not use N'' for the string, and insert the above data like:

    insert into alma2 values ('?p')

    the table will not store the above string, and a query will return

    "?p"

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • July 31, 2007 at 2:05 am

    #723493

    Hi Gift Peddie,

    Luckily nvarchar uses Unicode to store characters, and Unicode includes almost all the characters from almost all languages; it has no problems storing any of the Greek symbols. There is no reason to change the collation for these nvarchar columns. It would certainly make sense though if you wanted to sort the string containing the Greek symbols based on Greek language rules .

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • July 31, 2007 at 6:22 am

    #723540

    Actually that is not correct because being Nvarchar just tells SQL Server the data is not ASCII it is the collation that tells SQL Server what language to store with the relevant code page because Greek is Windows code 1253 and not 1252 like the rest of Western Europe.

     

    Kind regards,
    Gift Peddie

  • July 31, 2007 at 7:58 am

    #723571

    Hi,

    not sure I get you right .

    With nvarchars if the input is Unicode, it is stored as Unicode (there is no conversion). Unlike with varchars that is just ASCII with codepages. Basically SQL Server just takes the UTF-16 representation, and stores that in the row record. In UTF-16, so both Western European, Greek or let's say Japanese characters are representable together, in the same row data in the same nvarchar column. For every character usually 2 bytes are used. Since virtually all characters can be represented (together) collation is not needed to store and retrieve the data.

    Simple example:

    create table alma2 ( a nvarchar(10))

    insert into alma2 values (N'???') -- replace ??? with some random Greek characters

    insert into alma2 values (N'???') -- replace ??? with some random Hiragana characters

    select a, cast(a as varbinary(20)) from alma2

    When you query this table both the Japanese and Greek characters will be accessible. You can see the binary representation of the nvarchar in the second column.

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • July 31, 2007 at 8:23 am

    #723598

    (Basically SQL Server just takes the UTF-16 representation, and stores that in the row record)

    (select a, cast(a as varbinary(20)) from alma2)

    SQL Server does not use UTF16 it uses UCS-2 which is different and I find it funny you are telling users to cast data to Varbinary to get what the person can get with simple collation change.  You are not the only person who thinks Nvarchar will solve the problem but trust me it does not I solve these every week of the year. 

     

    Kind regards,
    Gift Peddie

  • August 2, 2007 at 8:09 am

    #724180

    Gift - the reason for casting to Varbinary was to allow the user to lookup the character and prove to themselves that no conversion occurred.

    Aren't we all supposed to be fluent in binary anyway, just like computers? (j/k)

  • August 2, 2007 at 8:31 am

    #724202

    hi All,

    now there is a slight change in the insertions. Is it possible to insert words equation objects in the table fields.

    It can be any thing for ex: y=-3x-4.right now i have typed it in and this very well will go in as a text but i have a bunch word files and all such equations were created using the inbuilt word equation editor and i need to store each word files content in the database.

    I think each equation object is a binary value. Is there a way i can push that in and then retrieve it as it was.

    Thanks

  • August 2, 2007 at 9:00 am

    #724216

    If the cast to varbinary will solve the problem it could also imply varbinary column could solve character conversion problems, a few weeks back a user picked my collation solution among many sugestion because he was getting character conversion issues with some languages.  The second issue is Nvarchar and Nvarchar(max) columns can be one page or more per row with collation it is one step, a cast to varbinary in a busy database is not so simple.  That is one of the reasons collation was added to DML(data manipulation) in 2005.

     

    Kind regards,
    Gift Peddie

  • August 2, 2007 at 9:10 am

    #724220

    If your files are Word documents that changes your needs because Word is best stored in IMAGE because all other data types you could loose formatting. The IMAGE data type comes with restrictions so create a table with a combination of data type and collation to see what gives you the best solution because Office documents are not easy to store in SQL Server.

     

     

    Kind regards,
    Gift Peddie

  • August 2, 2007 at 9:39 am

    #724235

    Gift,

    but i do not want to store the entire word document in the database. I want to extract the contents from the document line by line and store it accordingly. the document is usually 5-6 lines. Is there a way i can attach the word document so that everyone can have a look at it.

  • August 2, 2007 at 10:29 am

    #724259

    If you want to extract and store use Greek collation, attach Word not really but IMAGE column store Word and PDF almost like attaching because your file will be inserted and returned.

     

    Kind regards,
    Gift Peddie

Viewing 13 posts - 1 through 12 (of 12 total)

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