Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
  • 2
1 Solution
 
Brian CroweDatabase AdministratorCommented:
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

0
 
Scott PletcherSenior DBACommented:
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
0
 
Brian CroweDatabase AdministratorCommented:
Try this...

SELECT [Id_Column]
      ,[MfrnumCond]
     ,[Sku]
     ,[LongDesc]
      ,[DateCreated]
      ,[DateUpdated]
FROM [WeeklyFile].[dbo].[FuncExt]
WHERE CAST(FuncExt.[Id_Column] AS INT) <= 2000000

Open in new window


Keep in mind that this is a non-sargable query that will require a table scan to execute so performance will be dependent on the size of the table.
0
 
FordraidersAuthor Commented:
Thanks Brian, worked great !!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now