locked
Special character β saved in db but becomes ß which causing LINQ join issue RRS feed

  • Question


  • i just notice initially character was β which saved in db and when data fetch from db then β becomes ß

    both are different charater. in db the column data type was VARCHAR(MAX) later i changed NVARCHAR(MAX) but still β saved as ß in db which causing main issues.

    so please tell me how to save this special char β in db as a result when fetch from db later then char should be β not ß

    when doing this below LINQ query then few records is not storing into result

    var tickerBrokerStandardDateLineitemValuesGroups = (from tickerBrokerStandardDateLineitemValue in TickerBrokerStandardDateLineitemValues
       join _10QK in dt10QKDataFile.AsEnumerable()
       on new
       {
    	   TabName = tickerBrokerStandardDateLineitemValue.TabName.ToString().Trim().ToUpper(),
    	   StandardLineItem = tickerBrokerStandardDateLineitemValue.StandardLineItem.ToString().Trim().ToUpper()
       }
       equals new
       {
    	   TabName = _10QK.Field<string>("TabName").ToString().Trim().ToUpper(),
    	   StandardLineItem = _10QK.Field<string>("StandardLineItem").ToString().Trim().ToUpper()
       }
       group tickerBrokerStandardDateLineitemValue by new
       {
    	   Section = _10QK.Field<string>("TabName").ToString(),
    	   LineItem = _10QK.Field<string>("StandardLineItem").ToString()
       } into tickerBrokerStandardDateLineitemValuesGroup
       select tickerBrokerStandardDateLineitemValuesGroup);

    please tell me how to save this char β in db in such a way when i will fetch that same data from db then it should not be return as ß rather should be return as β

    in short how to save beta sign β in sql server ? i have stored beta sign β in nvarchar(max) type column but when i retrieve the data from db then β sign becomes something like ß which is wrong. 

    thanks



    • Edited by Sudip_inn Tuesday, October 6, 2020 7:57 PM
    Tuesday, October 6, 2020 6:22 PM

Answers


  • 	(                                                        
    	Section   VARCHAR(100),                                                        
    	LineItem  VARCHAR(100),                                                        
    	StandardDate VARCHAR(20),                                                        
    	StandardValue VARCHAR(20),                                                        
    	XFundCode  VARCHAR(20),            
    	ActualProvidedByCompany VARCHAR(3)            
    	)                     	

    You define all columns as VARCHAR = ASCII code, therefore you never get the real character. You have to define the data types as NVARCHAR = Unicode.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Sudip_inn Wednesday, October 7, 2020 2:39 PM
    Wednesday, October 7, 2020 6:16 AM
  • Hi Sudip_inn,

    Thank you for posting here.

    Try to use prefix Unicode character string:

    insert into TestTable1 values(5,'β')
    insert into TestTable1 values(6,N'β')


    Please check the following links for an explanation of that N.

    What does N' stands for in a SQL script ? (the one used before characters in insert script)

    Unicode strings

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 7, 2020 2:59 AM
  • Hi Sudip_inn,

    TRIM (N @ LineItem) is not the correct usage. N is used for strings instead of variables.

    I'm not proficient in Sql, I can't reproduce your problem, but I think Olaf Helper should be right. Use NVarChar to replace VarChar wherever such characters may appear, like this:

    DECLARE @idoc INT, @doc NVARCHAR(1000);  
    SET @doc =N'  
    <ROOT>  
    <Customer CustomerID="VINET" ContactName="Paul Henriot">  
       <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
          <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
          <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>  
       </Order>  
    </Customer>  
    <Customer CustomerID="LILAS" ContactName="β">  
       <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">  
          <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>  
       </Order>  
    </Customer>  
    </ROOT>';  
    --Create an internal representation of the XML document.  
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;  
    -- Execute a SELECT statement that uses the OPENXML rowset provider.  
    SELECT    *  
    FROM       OPENXML (@idoc, '/ROOT/Customer',1)  
                WITH (CustomerID  VARCHAR(10),  
                      ContactName NVARCHAR(20));  

    The example comes from this document.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Wednesday, October 7, 2020 2:40 PM
    Wednesday, October 7, 2020 8:00 AM

All replies

  • Hi Sudip_inn,

    Thank you for posting here.

    Try to use prefix Unicode character string:

    insert into TestTable1 values(5,'β')
    insert into TestTable1 values(6,N'β')


    Please check the following links for an explanation of that N.

    What does N' stands for in a SQL script ? (the one used before characters in insert script)

    Unicode strings

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 7, 2020 2:59 AM
  • Here i am showing how i am inserting data. i am passing below xml to store procedure and from there in cursor i insert data one by one. here is special char in lineitem <LineItem>β Thalassemia</LineItem>     

    Cursor fetch statement store data in variable and there how can i store N along with data ?

    	<?xml version="1.0" encoding="utf-16"?>                                          
    	<Root>                                          
    	  <PeriodicalData>                                          
    		<Section>Consensus Model</Section>                                          
    		<LineItem>Net Sales</LineItem>                                          
    		<XFundCode>TRIN</XFundCode>                                          
    		<StandardDate>2010 FY</StandardDate>                                          
    		<StandardValue>65225</StandardValue>                                          
    	  </PeriodicalData>                                          
    	  <PeriodicalData>                                          
    		<Section>Consensus Model</Section>                                          
    		<LineItem>β Thalassemia</LineItem>                                          
    		<XFundCode>TRIN</XFundCode>                                          
    		<StandardDate>2011 FY</StandardDate>                                          
    		<StandardValue>108249</StandardValue>                                          
    	  </PeriodicalData>                                          
    	</Root>  
    
    	Exec sp_xml_preparedocument @XMLFormat OUTPUT, @BogyXML                                           
    										  
    	DECLARE CURRECORD                                                        
    	CURSOR LOCAL FOR                                                        
    	SELECT Section,LineItem,StandardDate,StandardValue,XFundCode,ActualProvidedByCompany                                                        
    	FROM OPENXML (@XMLFORMAT, '/Root/PeriodicalData', 2)                                                        
    	WITH                                               
    	(                                                        
    	Section   VARCHAR(100),                                                        
    	LineItem  VARCHAR(100),                                                        
    	StandardDate VARCHAR(20),                                                        
    	StandardValue VARCHAR(20),                                                        
    	XFundCode  VARCHAR(20),            
    	ActualProvidedByCompany VARCHAR(3)            
    	)                     
    										  
    	-- open cursor                                                        
    	OPEN CURRECORD                                                        
    	FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode,@ActualProvidedByCompany                                                        
    														
    	-- iterate in cursor to fetch value                                                        
    	WHILE (@@FETCH_STATUS=0)                                                        
    	BEGIN    
    
    	INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),TRIM(@LineItem),TRIM(@XFundCode),'I',@UserID)   
    
    	FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode,@ActualProvidedByCompany                                             
    	END                                                        
    														   
    						 
    	CLOSE CURRECORD                                                        
    	DEALLOCATE CURRECORD    

    this way i am inserting data

    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),TRIM(@LineItem),TRIM(@XFundCode),'I',@UserID) 

    here can i use this  TRIM(N@LineItem) ?

    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),TRIM(N@LineItem),TRIM(@XFundCode),'I',@UserID) 

    please guide me. thanks


    • Edited by Sudip_inn Wednesday, October 7, 2020 4:28 AM
    Wednesday, October 7, 2020 4:24 AM

  • 	(                                                        
    	Section   VARCHAR(100),                                                        
    	LineItem  VARCHAR(100),                                                        
    	StandardDate VARCHAR(20),                                                        
    	StandardValue VARCHAR(20),                                                        
    	XFundCode  VARCHAR(20),            
    	ActualProvidedByCompany VARCHAR(3)            
    	)                     	

    You define all columns as VARCHAR = ASCII code, therefore you never get the real character. You have to define the data types as NVARCHAR = Unicode.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Sudip_inn Wednesday, October 7, 2020 2:39 PM
    Wednesday, October 7, 2020 6:16 AM
  • Hi Sudip_inn,

    TRIM (N @ LineItem) is not the correct usage. N is used for strings instead of variables.

    I'm not proficient in Sql, I can't reproduce your problem, but I think Olaf Helper should be right. Use NVarChar to replace VarChar wherever such characters may appear, like this:

    DECLARE @idoc INT, @doc NVARCHAR(1000);  
    SET @doc =N'  
    <ROOT>  
    <Customer CustomerID="VINET" ContactName="Paul Henriot">  
       <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
          <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
          <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>  
       </Order>  
    </Customer>  
    <Customer CustomerID="LILAS" ContactName="β">  
       <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">  
          <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>  
       </Order>  
    </Customer>  
    </ROOT>';  
    --Create an internal representation of the XML document.  
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;  
    -- Execute a SELECT statement that uses the OPENXML rowset provider.  
    SELECT    *  
    FROM       OPENXML (@idoc, '/ROOT/Customer',1)  
                WITH (CustomerID  VARCHAR(10),  
                      ContactName NVARCHAR(20));  

    The example comes from this document.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Wednesday, October 7, 2020 2:40 PM
    Wednesday, October 7, 2020 8:00 AM