Fordraiders
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].[FuncEx t]
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
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].[FuncEx
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Brian, worked great !!
Here is a short example to demonstrate the issue with strings as numbers:
Open in new window