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
 General SQL Server Forums
 New to SQL Server Programming
 Select Where Value is INTERGER

Author  Topic 

jwarren
Starting Member

3 Posts

Posted - 2014-02-12 : 10:56:28
I am trying to select only values within a column are intergers.

My statement looks like this:
(I am using MSSQL querying into a Pervasive DB via a Linked Server)

[CODE]SELECT Invoice, Invoice_Date, Description, INVOICE_Code_1
FROM API_PENDING__INVOICE
WHERE (API_PENDING__INVOICE.INVOICE_CODE_1) is integer???[/CODE]

Obviously, this is not working... Any tips?

Thanks!

Jonathan Warren

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-12 : 11:27:50
what is the data type of [INVOICE_CODE_1] ?

If it is some flavor of character then the code to get only integers will be quite ugly. You can make the code a little simpler but then it will not be entirely fool proof.

this will return values that only contain characters 0 through 9. It will exclude values that contain any other character.
where [INVOICE_CODE_1] not like '%[^0-9]%'

but it will return things that are not inteters like '9999999999999999999999'.
and it will not return '-123' which is an integer.

So to cover all the edge cases the code becomes quite ugly. If you need to guarantee only integers then the column should be data type [INT].

Be One with the Optimizer
TG
Go to Top of Page

alexsts
Starting Member

13 Posts

Posted - 2014-02-12 : 11:29:14
What datatype this .INVOICE_CODE_1 is?
If it is varchar(nvarchar, etc)I would use temp table to select all values based on evaluation of the value and if it is can be converted to int and then create JOIN between your table and TEMP table.
Something like that:

Select INVOICE_Code_1 INTO #TMPTable From API_PENDING__INVOICE WHERe ISNUMERIC(INVOICE_Code_1)= 1)

SELECT Invoice, Invoice_Date, Description, INVOICE_Code_1
FROM API_PENDING__INVOICE
WHERE (API_PENDING__INVOICE.INVOICE_CODE_1 in (select INVOICE_Code_1 from #TMPTable))


That should gives you all wanted rows.
If this is inline SQL (would not ever recommend to use in-lines, but it is your application)
you can do:

SELECT Invoice, Invoice_Date, Description, INVOICE_Code_1
FROM API_PENDING__INVOICE
WHERE ISNUMERIC(INVOICE_Code_1)= 1)

Cheers

Alex
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-12 : 11:37:45
I like alexsts idea of another table with valid values.
But isnumeric will return values that are not integers. And where col in (select ...) is not the most efficient way to do it.

What I would consider is either a table or function that has/returns a large enough range of integers for your purpose and then INNER JOIN to that table:

from yourTable yt
join numbers n on convert(varchar(15), n.number) = yt.[INVOICE_CODE_1]



Be One with the Optimizer
TG
Go to Top of Page

jwarren
Starting Member

3 Posts

Posted - 2014-02-12 : 11:40:34
TG,

That will actually work for my situation.

Basically this is part of a larger script that syncs data. The problem now is it's pulling all data, no matter the type, which cause another part of the script to fail, I just want to exclude those.

I wish I could change the column type but it's locked down by the software developers and the company doesn't want to risk manually doing that change.

Thank you so Much!!!!!!

Jonathan Warren
Go to Top of Page

alexsts
Starting Member

13 Posts

Posted - 2014-02-12 : 11:43:11
quote:
Originally posted by TG

I like alexsts idea of another table with valid values.
But isnumeric will return values that are not integers. And where col in (select ...) is not the most efficient way to do it.

What I would consider is either a table or function that has/returns a large enough range of integers for your purpose and then INNER JOIN to that table:

from yourTable yt
join numbers n on convert(varchar(15), n.number) = yt.[INVOICE_CODE_1]



Be One with the Optimizer
TG


Actually that is why we have it like that:

WHERE ISNUMERIC(INVOICE_Code_1)= 1)

It will only selects rows where value are numeric or could be converted to int.

Alex
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-12 : 12:00:07
quote:
Originally posted by alexsts
Actually that is why we have it like that:

WHERE ISNUMERIC(INVOICE_Code_1)= 1)

It will only selects rows where value are numeric or could be converted to int.



These are all isNumeric = 1 but none will convert to int:

select v
from
(
select '34000000000' union all
select '1.23' union all
select '6.5e-7'
) d (v)
where isNumeric(v) = 1


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -