Query to see if an int field starts with a certain number

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!Convert it to string, perform a substring, and then do your comparison.

Perhaps: substring(cast([thisfield] as varchar(50)),1,1)|||I don't know how the performance of this will compare, but if thisfield is non-negative, this should work as well:

[thisfield] / power(10, cast(log10([thisfield] as int))

Cheers,
-Isaac
|||

I hate to ask this, but the giant pink elephant in the room is "how do you have an int that doesn't start with a number?" What it sounds like you have is a column of string values that may or may not be an integer, and you want to see if the first character of the string is a number, right? For this it is:

thisColumn like '[1234567890]%'

But if the column is supposed to only contain integers, the best way to make sure that they are integers is to create the column using an integer datatype.

Query to return certain result if meet requirement

Hi,
Is there anyway to query 7 table from MSSQL , If any of the table consist of 0 then the value 0 is return. If all 7 table dont consist 0 value in the table, value 1 is return

Regards
Mandrakeooi

Quote:

Originally Posted by Mandrakeooi

Hi,
Is there anyway to query 7 table from MSSQL , If any of the table consist of 0 then the value 0 is return. If all 7 table dont consist 0 value in the table, value 1 is return

Regards
Mandrakeooi

Try

select case count(*) when 0 then 1 else 0 end
from (
select myColumn from table1 where myColumn=0
union all
select myColumn from table2 where myColumn=0
union all
...
select myColumn from table7 where myColumn=0)

Query Three Tables - Please Help

Hi

I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)

To get the data I need - I need to query three tables.

Stockitem - This has the column "itemid"
Stockitemmemo - This has the column "itemid" and "timeanddatecreated"
Binitem - This has the column "itemid" and "binname"

My results must be based on the following criteria.....

All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.

I do have two questions based on the above...

1. Does it make sense what I need?
2. Is it possible

:confused:

Any help would be gratefully received.

Thanks

SimbaOK, help us out.

Read the hint sticky at the top of the forum and post what it asks for|||Possibly a straight Forward


SELECT Stockitem.itemid
,Stockitemmemo.Memo
,Binitem.binname
FROM Stockitem
INNER JOIN Stockitemmemo ON
Stockitem.itemid = Stockitemmemo.itemid
INNER JOIN Binitem ON
Stockitem.itemid = Binitem.itemid
WHERE Binitem.binname LIKE'%T%'
AND Stockitemmemo.timeanddatecreated <'01/02/2007'


Everyones gotta start somewhere :shocked:

Beware If there is NO matching record in either Stockitemmemo or Binitem table then the Stockitem row will NOT be returned - You would need to Consider a LEFT Join to deal with that.

GW

GW|||Your an absolute legend thanks very much! Appreciate your help.

Thanks again.:beer:|||You're Welcome :)|||What's in an absolut legend anyway? Sounds interesting, and my banana Kamikaze's are starting to get me in trouble, so I need a change.|||LOL Tall
What's in an absolut legend anyway? in the UK we call it "Horses for courses"

I'll gladly leave the difficult one's for the u true legends.

Not sure what a banana Kamikaze is but sounds like you need a TallBoy http://www.dbforums.com/showpost.php?p=6287499&postcount=30 - LOL

GW