String Range... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String Range...

Author

Topic

mohit_sharan
Starting Member

22 Posts

Posted - 2003-12-02 : 23:59:13

Hi,

Below is the table description as well as its data :

Column_name Type Length
--------------- ------------- -----------
table_name varchar 50
from_cusip char 9
to_cusip char 9

table_name from_cusip to_cusip
------------ ---------- ---------
A1 1 100
A2 101 200
A3 201 300

My query is to fine out the to which table_name the cusip '21' belongs. I am getting 'A3' but the actual result should be 'A1'. The code which I used to find out the table_name by passing the cusip is as follows :

create procedure sp_wsxmain_Lookup (@var char(9), @temp_table varchar(50) OUTPUT)
AS
begin
declare @f_cusip char(9),
@t_cusip char(9),
@tem_table varchar(50)
DECLARE master_cursor CURSOR FOR SELECT table_name,from_cusip,to_cusip FROM wsxmain_Master
open master_cursor
fetch master_cursor into @tem_table,@f_cusip,@t_cusip
WHILE @@FETCH_STATUS = 0
BEGIN
if (@var >= @f_cusip and @var <= @t_cusip)
begin
select @temp_table =(select @tem_table)
break
end
else
fetch master_cursor into @tem_table,@f_cusip,@t_cusip
END
close master_cursor
DEALLOCATE master_cursor
end

-------------------
to retrive the value is as follows :

declare @temp_table varchar(50),@var char(9)
begin
set @var ='12'
exec sp_wsxmain_Lookup @var,@temp_table = @temp_table OUTPUT
select 'Table Name is ',@temp_table
end

--------
Can u please help me is finding it out how to look for a value when the data is of string. As we have alphanumeric value in from_cusip as well as to_cusip.

Thanks with Regards.

-Mohit.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-03 : 02:02:55

First, you dont need a cursor to do this.
Second, why are the numbers stored in a char column?
Third, the answer you are getting is correct, since "21" (string) does fall between "201" and "300" (both strings)

This query might do all that you need:


SELECT table_name FROM wsxmain_Master WHERE CAST(@var AS smallint) BETWEEN CAST(from_cusip AS smallint) AND CAST(to_cusip AS smallint)

Owais

Please excuse my driving, I am reloading.

mohit_sharan
Starting Member

22 Posts

Posted - 2003-12-03 : 02:20:05

Hi,

Thanks for the solution. But,we have a requirement of storing alphnumeric values in it. Now supose if we have something like '00163T109' or '00504P105'. In that case how to search for a particular value in that case in the range.

Regards.
-Mohit.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-03 : 04:13:59

In that case, the results you are getting are correct as I mentioned earlier. By ASCII or binary value comparision (which will be required for string comparisions), "21" does fall between "201" and "300". How will your ranges be set up then? I suggest you add another column to wsxmain_Master, which indicates if the range is numeric or alphanumeric. For example, it could be a bit column called "NumericRange" set to 1 for numeric ranges and 0 for alphanumeric ranges. Dont mix both the types of ranges. Then in the procedure you can query for the appropriate table name depending on the type of parameter supplied:


IF ISNUMERIC(@var)
  SELECT table_name FROM wsxmain_Master 
      WHERE CAST(@var AS smallint) BETWEEN CAST(from_cusip AS smallint) AND CAST(to_cusip AS smallint)
      AND NumericRange = 1

ELSE
  SELECT table_name FROM wsxmain_Master 
      WHERE @var BETWEEN from_cusip AND to_cusip AND NumericRange = 0

Owais

Please excuse my driving, I am reloading.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources