locked
Inserting Greek symbols into SQL Server 2005 RRS feed

  • Question

  • Hi,

    I have a user attempting to save the alpha (α) character into my database (collation is Latin1_General_CI_AI).  Is this possible with the collation selected?  It saves as α

    Thanks in advance,

    Mark

    Tuesday, May 18, 2010 8:33 PM

Answers

  • Yes, you can create a new column with nvarchar(max), transfer data from original column, remove the old column, rename the new column to the old one.

    However, if that field was already ntext, I'm not sure why do you have this problem. Are you sure you send data as unicode from your application?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by mah-skeet Thursday, May 20, 2010 7:58 PM
    Thursday, May 20, 2010 3:51 AM
    Answerer

All replies

  • What is the type of the column? It should be either nvarchar or nchar.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 18, 2010 9:48 PM
    Answerer
  • Are you using a web application? It kind of seems the web application encodes it. Normally you can save alpha character into a nvarchar or nchar column.
    Wednesday, May 19, 2010 6:48 AM
  • Hi Naom,

    Thanks for the reply.  No, the column is ntext.  Not sure why this type was chosen (I only inherited this system/database).  Is it possible to go from ntext to nvarchar(max)? 

    Thank you,

    Mark

    Wednesday, May 19, 2010 2:06 PM
  • Yes, you can create a new column with nvarchar(max), transfer data from original column, remove the old column, rename the new column to the old one.

    However, if that field was already ntext, I'm not sure why do you have this problem. Are you sure you send data as unicode from your application?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by mah-skeet Thursday, May 20, 2010 7:58 PM
    Thursday, May 20, 2010 3:51 AM
    Answerer
  • Hi Naom,

    Ok, I think I'm starting to narrow the issue down to MS Access.  My SQL Server saves the α as α and the webpages display the α fine.  But I have a report that is built in MS Access that isn't displaying the α correctly.  It shows the stored value.  Any idea how to fix this?

    Thanks again for your help,

    Mark

    Thursday, May 20, 2010 1:41 PM
  • Mark,

    You can store Greek textual information the following way using UNICODE 2 bytes format. It is up to the application software to display it correctly.

    CREATE TABLE #Greek (
    ID INT IDENTITY(1,1) PRIMARY KEY, 
    Word nvarchar(max),
    ModifiedDate datetime default (getdate()));
    INSERT #Greek (Word) VALUES (N'Ηλεκτρονικά Παιχνίδια')
    SELECT * FROM #Greek
    /*
    ID	Word	ModifiedDate
    1	Ηλεκτρονικά Παιχνίδια	2010-05-20 09:54:09.290
    */
    GO
    DROP TABLE #Greek
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, May 20, 2010 1:54 PM
  • α is the HTML entity value for the alpha symbol.  Your problem is related to HTML and to your web site and has nothing to do with SQL-Server or with Access (which doesn't understand HTML).
     
    If you want to see the alpha symbol in Access, you will have to change the programmation of your web site so that it will be stored as its unicode value and not as an HTML entity value.
    • Proposed as answer by Naomi NEditor Sunday, May 23, 2010 5:58 AM
    Sunday, May 23, 2010 5:13 AM
  • Is there any way to query your above example with simple SQL select?

     

    Because in my case the statement SELECT * FROM GREEK WHERE WORD = 'Ηλεκτρονικά Παιχνίδια' doesn't return anything.

    Only SELECT * FROM GREEK returns all row(s) of the table but I want a condition WHERE.

    Please, can anybody shed a light on this?

     

    Saturday, May 29, 2010 11:54 PM
  • Try UNICODE literal:

    SELECT * FROM GREEK WHERE WORD = N'Ηλεκτρονικά Παιχνίδια'


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, May 30, 2010 6:47 AM