CODEWITHSUNDEEP
sql-serversql-server-2005
DForck42
DForck42

Reputation: 20357

Get records that are only numbers

I have this query running. For the most part it works ok, but there are a few records that have non-number parts to them and this causes issues down the line. I need to be able to select just the records that are integers.

select distinct SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
        FROM         dbo.PA01201 AS PA01201_1
        where PAPROJNUMBER>'0'

For example, a part of the result list looks like this:

68145
68146
68147
7762A
99999
LABOR

i want all of those except 7762A and LABOR. How can I modify the where clause to achieve that efficiently?

Upvotes: 3

Views: 102

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Adding '.0e0' to the substring will insure that the ISNUMERIC only returns valid integers.

select distinct SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
    FROM dbo.PA01201 AS PA01201_1
    where PAPROJNUMBER>'0'
        AND ISNUMERIC(SUBSTRING(PAPROJNUMBER, 1, 5) + '.0e0') = 1

Upvotes: 6

John Hartsock
John Hartsock

Reputation: 86882

You could do the following

SELECT
  SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
FROM dbo.PA01201
WHERE SUBSTRING(PAPROJNUMBER, 1, 5) LIKE '[0-9][0-9][0-9][0-9][0-9]'

Upvotes: 2

AllenG
AllenG

Reputation: 8190

Add ISNUMERIC() to your where clause:

select distinct SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
from DBO.PA01201 AS PA01201_1
where PAPROJNUMBER > '0' AND ISNUMERIC(SUBSTRING(PAPROJNUMBER, 1, 5))

Alternatively, you can drop the > comparison:

select distinct SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
from DBO.PA01201 AS PA01201_1
where ISNUMERIC(SUBSTRING(PAPROJNUMBER, 1, 5))

Upvotes: -2

Related Questions