locked
€ symbol RRS feed

  • Question

  • hi,

    im making something and i need to add a '€' in a column. but i can't find how to do that.

    thank you if you can help me!

    Saturday, October 10, 2015 11:32 AM

Answers

  • Hi,

    I have no problem with such symbol.

    use test
    go
    
    create table tab1 (col1 nvarchar(20))
    
    insert into tab1 values ('€')
    
    select * from tab1
    

    Try to define the column using "nvarchar".


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by NeleSm Monday, October 12, 2015 4:18 PM
    Saturday, October 10, 2015 1:54 PM
  • I should add that if the target column is char or varchar, the collation code page must support the symbol.  For the Euro, one of the following collations below are required.  You must use nchar or nvarchar for other collations.

    SELECT name FROM fn_helpcollations()
    WHERE COLLATIONPROPERTY( name, 'CodePage' ) IN(
    	 874
    	,936
    	,1250
    	,1251
    	,1252
    	,1253
    	,1254
    	,1255
    	,1256
    	,1257
    	,1258
    );


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Saturday, October 10, 2015 2:11 PM added unocide note
    • Marked as answer by Eric__Zhang Tuesday, October 13, 2015 7:30 AM
    Saturday, October 10, 2015 2:09 PM

All replies

  • Not sure I under your question but you should be able to use a standard INSERT statement:

    INSERT INTO dbo.CurrencyTable (CurrencySymbol) VALUES('€');


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 10, 2015 1:50 PM
  • Hi,

    What is your column type where you want to add the ''?

    It is just simple to add like below,

    Create table #temp 
    ( name nvarchar(10)
    )
    
    insert into #temp values ('€')
    select * from #temp

    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Saturday, October 10, 2015 1:52 PM
  • Hi,

    I have no problem with such symbol.

    use test
    go
    
    create table tab1 (col1 nvarchar(20))
    
    insert into tab1 values ('€')
    
    select * from tab1
    

    Try to define the column using "nvarchar".


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by NeleSm Monday, October 12, 2015 4:18 PM
    Saturday, October 10, 2015 1:54 PM
  • I should add that if the target column is char or varchar, the collation code page must support the symbol.  For the Euro, one of the following collations below are required.  You must use nchar or nvarchar for other collations.

    SELECT name FROM fn_helpcollations()
    WHERE COLLATIONPROPERTY( name, 'CodePage' ) IN(
    	 874
    	,936
    	,1250
    	,1251
    	,1252
    	,1253
    	,1254
    	,1255
    	,1256
    	,1257
    	,1258
    );


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Saturday, October 10, 2015 2:11 PM added unocide note
    • Marked as answer by Eric__Zhang Tuesday, October 13, 2015 7:30 AM
    Saturday, October 10, 2015 2:09 PM
  • Hi Dan,

    How did you get the CodePage list?  Thanks.





    Kalman Toth Database & OLAP Architect Artificial Intelligence
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014



    Saturday, October 10, 2015 2:30 PM
  • How did you get the CodePage list?

    I identified the code pages manually by running a script for each distinct collation code page.  Below is a script that generates the needed SQL.  In the case of multiple collations with the same code page, it doesn't matter which collation is chosen for query.

    WITH
    	Collation AS (
    		SELECT
    			  name AS CollationName
    			, COLLATIONPROPERTY( name, 'CodePage' ) AS CodePage
    			, ROW_NUMBER() OVER(PARTITION BY COLLATIONPROPERTY( name, 'CodePage' ) ORDER BY name) AS RowNum
    		FROM fn_helpcollations()
    		WHERE COLLATIONPROPERTY( name, 'CodePage' ) > 0
    	)
    SELECT N'SELECT ' + CAST(CodePage AS nvarchar(4)) + N' AS CodePage, ''€'' COLLATE ' + CollationName
    FROM Collation
    WHERE RowNum = 1;

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Saturday, October 10, 2015 3:05 PM change script to use Unicode literal
    • Proposed as answer by Kalman Toth Saturday, October 10, 2015 5:09 PM
    Saturday, October 10, 2015 3:04 PM
  • Thanks Dan.  Results below. Why "Estonian"? Why not Polish or Latvian?

    Who or what organization in Estonia decided to put in the Euro symbol?

    We live in a confusing global village.

    SELECT 437 AS CodePage, '€' COLLATE SQL_Latin1_General_CP437_BIN
    SELECT 850 AS CodePage, '€' COLLATE SQL_1xCompat_CP850_CI_AS
    SELECT 874 AS CodePage, '€' COLLATE Thai_100_BIN
    SELECT 932 AS CodePage, '€' COLLATE Japanese_90_BIN
    SELECT 936 AS CodePage, '€' COLLATE Chinese_PRC_90_BIN
    SELECT 949 AS CodePage, '€' COLLATE Korean_100_BIN
    SELECT 950 AS CodePage, '€' COLLATE Chinese_Hong_Kong_Stroke_90_BIN
    SELECT 1250 AS CodePage, '€' COLLATE Albanian_100_BIN
    SELECT 1251 AS CodePage, '€' COLLATE Azeri_Cyrillic_100_BIN
    SELECT 1252 AS CodePage, '€' COLLATE Breton_100_BIN
    SELECT 1253 AS CodePage, '€' COLLATE Greek_100_BIN
    SELECT 1254 AS CodePage, '€' COLLATE Azeri_Latin_100_BIN
    SELECT 1255 AS CodePage, '€' COLLATE Hebrew_100_BIN
    SELECT 1256 AS CodePage, '€' COLLATE Arabic_100_BIN
    SELECT 1257 AS CodePage, '€' COLLATE Estonian_100_BIN
    SELECT 1258 AS CodePage, '€' COLLATE Vietnamese_100_BIN





    Kalman Toth Database & OLAP Architect Artificial Intelligence
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014



    Saturday, October 10, 2015 3:12 PM
  • Thanks Dan.  Results below. Why "Estonian"? Why not Polish or Latvian?

    It doesn't really matter which collation is used for this test since all collations that use the same code page have the same character set.  I arbitrarily chose the first one alphabetically in this query.  For the code pages that returned the Euro symbol by the generated script, I specified those in the IN clause of the first query I posted to return the complete list of all collations that could be used (2859 collations on my SQL Server 2014 instance).

    Who or what organization in Estonia decided to put in the Euro symbol?

    Code page 1257 is a Windows code page (Windows Baltic) so that would be Microsoft.

    https://en.wikipedia.org/wiki/Windows-1257
    https://en.wikipedia.org/wiki/Code_page


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 10, 2015 4:50 PM
  • use test
    go
    
    create table tab1 (col1 nvarchar(20))
    
    insert into tab1 values (N'€')
    
    select * from tab1

    Sometimes and this might be one of those occasions, you need to add the N before the quotes so that SQL Server treats the character as Unicode.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Naomi N Sunday, October 11, 2015 2:03 AM
    Saturday, October 10, 2015 5:43 PM
  • SQL is based on a tiered architecture. The database tier handles all of the database retrieval and data integrity. But nothing else. The data display and formatting is done in presentation layers that get data from the database layer. They are not mixed together the way you believe. 

    What you seem to want is 1960's COBOL in which there a single monolithic program that does computations, data retrieval and display formatting with currency symbols.  Your whole mindset is fundamentally wrong. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, October 11, 2015 2:06 AM