locked
How to convert the column Data type RRS feed

  • Question

  • Hi Every one,

    i have a two tables,one table column datatype is varchar(20),onther table data type is nvarchar.i am using to join the two table column.but it does'nt shown rows.it has common column.how to use the convert function and how can i join both table?


    Vinodkumar.kapa

    Sunday, March 25, 2012 7:17 AM

Answers

  • Explicit conversion example from UNICODE (2 bytes) to ASCII (1 byte):

    DECLARE @ColA varchar(20) = 'Hétfo'
    DECLARE @ColX nvarchar(20) = N'Hétfő'  -- Hungarian for Monday
    SELECT 'Match' WHERE @ColA = CONVERT(varchar(20),@ColX)
    -- Match

    Related article:

    http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server



    • Marked as answer by KJian_ Monday, April 2, 2012 1:14 AM
    • Edited by Kalman Toth Monday, November 5, 2012 3:01 PM
    Sunday, March 25, 2012 7:24 AM
  • i have a two tables,one table column datatype is varchar(20),onther table data type is nvarchar.i am using to join the two table column.but it does'nt shown rows.it has common column.how to use the convert function and how can i join both table?

    SQL Server will implictly convert varchar to nvarchar for the join expression because nvarchar has a higher data type precedence.  If the underlying characters are different like Kalman suggested, that would explain why no rows are returned.  Explictly converting Unicode characters to varchar will change characters not present in the collation to an alternate character (e.g. N'ő' to 'o') or not map at all (e.g. N'丶' to '?').  Forcing an equality comparison in this way may or may not be desired.

    Be aware that performace could suffer since conversion may prevent indexes on the joined columns from being used efficiently.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by KJian_ Monday, April 2, 2012 1:13 AM
    Sunday, March 25, 2012 5:01 PM

All replies

  • Explicit conversion example from UNICODE (2 bytes) to ASCII (1 byte):

    DECLARE @ColA varchar(20) = 'Hétfo'
    DECLARE @ColX nvarchar(20) = N'Hétfő'  -- Hungarian for Monday
    SELECT 'Match' WHERE @ColA = CONVERT(varchar(20),@ColX)
    -- Match

    Related article:

    http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server



    • Marked as answer by KJian_ Monday, April 2, 2012 1:14 AM
    • Edited by Kalman Toth Monday, November 5, 2012 3:01 PM
    Sunday, March 25, 2012 7:24 AM
  • i have a two tables,one table column datatype is varchar(20),onther table data type is nvarchar.i am using to join the two table column.but it does'nt shown rows.it has common column.how to use the convert function and how can i join both table?

    SQL Server will implictly convert varchar to nvarchar for the join expression because nvarchar has a higher data type precedence.  If the underlying characters are different like Kalman suggested, that would explain why no rows are returned.  Explictly converting Unicode characters to varchar will change characters not present in the collation to an alternate character (e.g. N'ő' to 'o') or not map at all (e.g. N'丶' to '?').  Forcing an equality comparison in this way may or may not be desired.

    Be aware that performace could suffer since conversion may prevent indexes on the joined columns from being used efficiently.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by KJian_ Monday, April 2, 2012 1:13 AM
    Sunday, March 25, 2012 5:01 PM