Solved

How to compare the data of smallint with varchar

Posted on 2004-10-05
6
331 Views
Last Modified: 2006-11-17
Hello, I have one field with type of smallint, and want to compared to another filed with type of varchar in differet table, both two fields have same meaning, now i want to compare them, how can i do a conversion, that say, Table A,field1: Line_nbr, varchar; Table B, field2:req_line_nbr. how can i do the comparsion of req_line_nbr with line_nbr? what function should i use? Thanks
0
Comment
Question by:shyline777
  • 2
6 Comments
 
LVL 4

Expert Comment

by:strangelynormal1
ID: 12230665
SELECT *
FROM [Table A] a
         ,[Table B] b
WHERE CONVERT(VARCHAR(20),a.Line_nbr) = b.req_line_nbr
0
 

Author Comment

by:shyline777
ID: 12237988
i did the convert and only return 18 which should be 42, i found the problem coused by my extract function LEFT which Return TAB character clude, so can you tell me how can i extracted the characters before a TAB used as a delimiter in a data field?  Thank you so much
0
 
LVL 4

Accepted Solution

by:
strangelynormal1 earned 50 total points
ID: 12239724
Okay, let's assume that the field only has tab as a possible extra character.

SELECT *
FROM [Table A] a
         ,[Table B] b
WHERE a.Line_nbr = CONVERT(SMALLINT,REPLACE(b.req_line_nbr,CHAR(9),''))
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question