Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

query not pulling in ID column correctly on query

sql server 2008 r2

I have an id_column that is varchar(50)

I have this query where I'm trying to pullout data in sequence piece by piece. into a text file.

[code][/
SELECT [Id_Column]
      ,[MfrnumCond]
     ,[Sku]
     ,[LongDesc]
      ,[DateCreated]
      ,[DateUpdated]
  FROM [WeeklyFile].[dbo].[FuncExt]
  where FuncExt.[Id_Column] <= '2000000'
  --where FuncExt.[ID_COLUMN] between  '2000000'  and '5000000'
  --where FuncExt.[ID_COLUMN] between  '5000000'  and '8000000'
  --where FuncExt.[ID_COLUMN] between  '8000000'  and '10000000'
  --where FuncExt.[ID_COLUMN] > '10000000'
code]

The query is pulling out records not in sequence ???

Thanks
fordraiders
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

You are trying to treat strings as numbers.  You will need to cast the ID_COLUMN to an integer if you want to use the BETWEEN operator as you are.

Here is a short example to demonstrate the issue with strings as numbers:

WITH cteNumber AS
(
	SELECT 1 AS Value
	UNION ALL
	SELECT Value + 1 AS Value
	FROM cteNumber
	WHERE Value < 100000
)
SELECT CAST(Value AS VARCHAR)
FROM cteNumber
ORDER BY CAST(Value AS VARCHAR)
OPTION (MAXRECURSION 0)

Open in new window

A varchar comparison works fundamentally different from a numeric comparison, since it is a simple left-to-right character comparison and not a whole value comparison.

For example:

SELECT *
FROM (
    values('2000000'),('214')
    ) AS FuncExt(Id_Column)
  where FuncExt.[ID_COLUMN] between  '2000000'  and '5000000'

You could add a check for the length of the column:

  where LEN(FuncExt.[Id_Column]) < 7
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[Id_Column] <= '2000000'
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[ID_COLUMN] between  '2000000'  and '5000000'
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[ID_COLUMN] between  '5000000'  and '8000000'
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[ID_COLUMN] between  '8000000'  and '9999999'
  --where LEN(FuncExt.[Id_Column]) >= 8
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fordraiders

ASKER

Thanks Brian, worked great !!