converting String to Number

Question

converting String to Number

keywestfl9

SSCertifiable

Points: 5215
More actions
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.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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?

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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)

justanewone Ten Centuries Points: 1398 More actions · Answer 4

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