Select Query problem...
-
I need to run a standard select but I am not getting any results. The problem is, the user input goes something like '200' and the value in the field I am comparing to is '000200' (contains leading zeros, for some reason that is beyond me). Also, the data type for the column, in my table, is char and I also need to know if my parameter needs to be of the same type. Does anyone have any suggestions/ideas to best approach this situation? Thanks, T
-
I need to run a standard select but I am not getting any results. The problem is, the user input goes something like '200' and the value in the field I am comparing to is '000200' (contains leading zeros, for some reason that is beyond me). Also, the data type for the column, in my table, is char and I also need to know if my parameter needs to be of the same type. Does anyone have any suggestions/ideas to best approach this situation? Thanks, T
The one who designed the database probably thought it would be a good idea to use
char. Could be right, could be wrong... but without speculating on that, here is one solution:create procedure FindMatch(@param varchar(100)) as
select * from TheTable where TheKey = right('000000' + @param, 6)I'm assuming the actual column type is char(6). Otherwise, adjust the
rightinvocation. Also, I've assumed you've already trimmed the parameter - otherwise, replace@paramwithltrim(rtrim(@param)). -
I need to run a standard select but I am not getting any results. The problem is, the user input goes something like '200' and the value in the field I am comparing to is '000200' (contains leading zeros, for some reason that is beyond me). Also, the data type for the column, in my table, is char and I also need to know if my parameter needs to be of the same type. Does anyone have any suggestions/ideas to best approach this situation? Thanks, T
Although the data type is char, but the data in the column is numeric then you can convert the field and then compare.
select * from table_name where cast(field_name as int) = 100;
Om Prakash Pant