Using WHERE and LEN with integers

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

Using WHERE and LEN with integers

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-17 : 13:15:55

Hello

I have a list of towns with addresses or GPS coordinates and would like to SELECT all fields that do not have any address whatsoever. I'm using ASP and SQL 2005.

So if a town has an address but no GPS = okay
If a town has a GPS address but no address = okay
A town has no address AND no GPS = not okay, need to SELECT it
for review

All rows are varchar including latitude and longitude but I could probably convert the GPS ones if need be.

MYPLACES TABLE
town, latitude, longitude, address
Toronto, 43.22,-78.33, 123 My Street
This place, 45.4332,-80.323, <null>
That place, 0.00,0,00,<null>
My place, 0.00,0.00,144 Elmdale

I would like to select all entries that don't have ANY address information (no GPS data AND no address). So 'that place' should be the only result returned.

Something like this:

select * from myplaces where cast(latitude as int) = 0 and cast(longitude as int) = 0 OR len(address) < 3

I want to select on two conditions: latitude AND longitude = 0, OR length of (address) column is less than 3. I'm using length of the address as <3 because there could be a blank space or carriage return in the address field and it won't necessarily be null.

Thank you for any help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:20:27

len will trim spaces so you may be better off using DATALENGTH instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-17 : 18:01:55

"select * from sites where CAST(latitude AS INT) = 0 and cast(longitude as int) = 0 OR datalength(address) < 3"

Conversion failed when converting the varchar value '43.13333' to data type int.

So the latitude is not working....wtf?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 18:03:41

whats the datatype of latitude? does it have non numeric data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-17 : 18:19:10

I am using varchar and values would be (example for latitude and longitude):
43.3333, -56.43433
46.00, -80.00
46.4389478,-80.38367

Currently pulling my hair out....

string = "insert into mytable (locid,lcreator,issue,status,ldate) select id,creator,'Missing Address and GPS','Open','" & now()+30 & "' from mytable2 where cast(latitude as integer) = 0 AND cast(longitude as integer) = 0 AND datalength(address) < 3"

Result:
Conversion failed when converting the varchar value '43.13333' to data type int.

I want to select all entires from mytable2 where latitude AND longitude = 0 (as integer) AND length of "address" < 3 meaning no appropriate address is there for this location. And to insert the result into mytable1. The extra stuff is the issue (missing address and gps), the creator of the location, and the time they have to fix it (in date).

Edit: I also tried converting the latitude and longitude fields to integer through SQL SMS and it gave the same error with the same value (43.13333)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-17 : 21:08:56

you can't cast ''43.13333' to int.

cast it to decimal(10,2) instead

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-17 : 23:59:26

strSQL = "insert into mytable (locid,lcreator,issue,status,ldate) select id,creator,'Missing Address and GPS','Notify','" & now()+30 & "' from mytable2 where cast(latitude as decimal(10,2)) = 0 AND datalength(address) < 3"

Microsoft OLE DB Provider for SQL Server error '80040e07'

Error converting data type varchar to numeric.

I'm getting pissed.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 00:04:19

you have invalid data. use isnumeric() to check your data

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-18 : 00:09:17

I don't know how... I'm still very new to SQL.

Right now I'm doing a loop to SELECT from table1 and INSERT into table2 and just ignoring the errors until I can get the proper code.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 00:15:54

[code]
select *
from yourtable
where isnumeric(longitude) <> 1
or isnumeric(latitude ) <> 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-18 : 00:22:04

strSQL = "insert into table1 (locid,lcreator,issue,status,ldate) select id,creator,'Missing Address and GPS','Notify','" & now()+30 & "' from sites where isnumeric(latitude) = 0 AND datalength(address) < 3"

inserts zero records... my latitude is varchar, do I have to combine CAST with isnumeric?

This is so frustrating.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 00:26:40

use the query i posted to verify your data


from  sites 
where (isnumeric(longitude) = 0
OR    isnumeric(latitute) = 0)
AND   datalength(address) < 3

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-18 : 00:39:30

strsql = "select id, creator from sites where (isnumeric(longitude) = 0 OR isnumeric(latitude) = 0) AND datalength(address) < 3"

...returns nothing, eof and bof are true. If I set the =0 to <> 0 then I receive results. I know for certain there are many entries with 0 for latitude and longitude values.

This is the only code that works so far, and I need to resume on error to get it to work. It's halting with an error but as far as I can tell all fields are numeric.

strsql = "select id, creator from mytable1 where cast(latitude as decimal(10,2)) = 0 OR cast(longitude as decimal(10,2)) = 0 AND datalength(address) < 3"
set objRS = objConn.Execute(strSQL)

if objrs.eof and objrs.bof then response.write "*"

while not objrs.eof
vid = objrs("id")
vcreator = objrs("creator")
strSQL2 = "insert into table2 (locid,lcreator,issue,status,ldate) values('" & vid & "','" & vcreator & "','Missing Address and GPS','Notify','" & now()+30 & "')"
set objRS2 = objConn.Execute(strSQL2)
objrs.movenext
wend

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 01:00:30

[code]
select id, creator
from mytable1
where (
isnumeric(latitude) = 0
or (isnumeric(latitude) = 1 and cast(latitude as decimal(10,2)) = 0)
or isnumeric(longitude ) = 0
or (isnumeric(longitude ) = 1 and cast(longitude as decimal(10,2)) = 0)
)
AND datalength(address) < 3
[/code]

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-18 : 01:18:34

strsql = "select id, creator from sites where (isnumeric(latitude)= 0 or (isnumeric(latitude)= 1 and cast(latitude as decimal(10,2)) = 0) OR isnumeric(longitude) = 0 or (isnumeric(longitude) = 1 and cast(longitude as decimal(10,2)) = 0)) AND datalength(address) < 3"

however I still receive "Error converting data type varchar to numeric. "

I appreciate the assistance you've given, but I don't know if this is ever going to work :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 01:40:19

maybe try this . . .

select 	id, creator 
from 	mytable1 
where 	(
		isnumeric(latitude)	= 0
	or	latitude 		= '0.00'
	or	isnumeric(longitude )	= 0
	or	longitude 		= '0.00'
	)
AND 	datalength(address) < 3

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-18 : 01:44:02

It's been a long night but I can now say that the last code snippet works 100%.

Thank You :) I don't know a lot about SQL except the basic commands so this forum is a great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources