Post reply

converting String to Number

  • August 11, 2009 at 3:51 pm

    #213446

    i am trying to join two tables

    TABLE 1

    EmployeeID:nvarchar(3)

    EmployeeNo:nvarchar(10)

    sample data:

    001,0000055

    035,0007878

    Table 2

    employeeid: nvarchar(3)

    employeeno: nvarchar(10)

    sample data

    1,55

    35,7878

    when i join using cast(table1.employeeid as int) = table2.employeeid and cast(table1.employeeno) = table2.employeeno

    i dont get any rows even though i expect one

    Please let me know what i go to change.

  • August 11, 2009 at 3:55 pm

    #1037768

    Have you tried casting both sides to INT? Can you add two columns to the tables, computed columns that convert the values to INT and then index the computed columns?

  • August 11, 2009 at 4:42 pm

    #1037774

    i tried to use cast on both. but no luck

    i tried to change the datatype of two fields of both tables to int.

    which actually makes both the table data even.

    still i am not suceeding the output

  • August 11, 2009 at 6:33 pm

    #1037796

    You have roughly 156 posts as of now... that means you're not really a newbie anymore and you've seen it said before... read the article in the first link in my signature below and post the data and table creation statements according to that article. Otherwise, a lot of folks won't even bother trying to make your setup to test with and you'll keep getting suggestions that lead to more questions instead of answers. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 12, 2009 at 4:37 am

    #1037905

    I don't understand why you are getting no results, the code below works for me...

    IF OBJECT_ID(N'Table1', 'U') Is Not Null DROP TABLE Table1

    IF OBJECT_ID(N'Table2', 'U') Is Not Null DROP TABLE Table2

    CREATE TABLE Table1(EmployeeID NVARCHAR(3), EmployeeNo NVARCHAR(10))

    INSERT INTO Table1 VALUES(N'001',N'0000055')

    INSERT INTO Table1 VALUES(N'035',N'0007878')

    CREATE TABLE Table2(EmployeeID NVARCHAR(3), EmployeeNo NVARCHAR(10))

    INSERT INTO Table2 VALUES(N'1',N'55')

    INSERT INTO Table2 VALUES(N'35',N'7878')

    SELECT

    Table1.EmployeeID AS 'Table1 EmployeeID',

    Table1.EmployeeNo AS 'Table1 EmployeeNo',

    Table2.EmployeeID AS 'Table2 EmployeeID',

    Table2.EmployeeNo AS 'Table2 EmployeeNo'

    FROM Table1

    JOIN Table2 ON CAST(Table1.EmployeeID AS INT) = Table2.EmployeeID

    AND CAST(Table1.EmployeeNo AS INT) = Table2.EmployeeNo

    Dave

    Dave

Viewing 5 posts - 1 through 4 (of 4 total)

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