Need some help with SQL.

[Pho3nix]

Hi guys,

I'm trying to filter data on a SQL view depening on whether eg. ID ends with 25.

Now because this isn't text wildcard search isn't working, I'm a little stumped on how to do it? Any advice ?

 

[agentrfr]

Parse as a string then filter ends?

 

[Digilus]

Hi guys,

I'm trying to filter data on a SQL view depening on whether eg. ID ends with 25.

Try something like (ID % 25) = 0 - basically ID divided by 25 returns no remainder - must mean it ends on 25 ? I can't think of a number that ends in 25 not divisible by 25 .. ?

 

[dabean]

Try something like (ID % 25) = 0 - basically ID divided by 25 returns no remainder - must mean it ends on 25 ? I can't think of a number that ends in 25 not divisible by 25 .. ?

That would catch multiples as well, eg. 50, 100.

 

[dequadin]

Cast the ID to a to a varchar and then use LIKE '%25'

Not so efficient but should work.

WHERE cast(ID as varchar(?YourLengthHere?)) LIKE '%25'

 

[PsyWulf]

You can still use wildcard operators with LIKE even if the field is a number field
http://www.techonthenet.com/sql/like.php
Just confirmed on a test DB here with Select * from table where where recordid like '500__'

 

[TsOx]

You dont have to cast it as a varchar to do comparision. Something like:

SELECT *
FROM YourTable
WHERE ID LIKE '%25'

Will just work even if ID is an integer or a real

*EDIT ^As the above says

 

[Digilus]

That would catch multiples as well, eg. 50, 100.

Yeah .. woops .. *runs to get more coffee*

 

[hedgehog]

SELECT *
FROM YourTable
WHERE (ID % 100) = 25

 

[Other Pineapple Smurf]

Try something like (ID % 25) = 0 - basically ID divided by 25 returns no remainder - must mean it ends on 25 ? I can't think of a number that ends in 25 not divisible by 25 .. ?

Actually you want a (ID%100)=25

 

[PsyWulf]

My or TsOx's methods would suffice as we're looking for a string match using a string operator on a field. Complicating it with modulus for no real reason

 

[hedgehog]

You may experience a performance penalty if you use LIKE instead of modulus

 

[PsyWulf]

Using modulus for a like is more likely to be a table scan operation,whereas LIKE with a wildcard at the start will likely trigger an index scan which while slow is faster than table scan

*Note assuming your table is indexed. If not there will still be an overhead for a math operation or a string comparison,the speed of these will depend on a few things like math-coprocessors

 

[poffle]

What about

select * from yourtable
where RIGHT(id,2) = 25

 

[PsyWulf]

Would need the single quotes,but yeah workable,similar performance hit

 

[Pho3nix]

Thanks guys, don't know where my head was at

Tried using

LIKE '%[37-40]

to find a range but this didn't work..

 

[Pho3nix]

Fixed it

WHERE RIGHT(ID, 2) BETWEEN 94 AND 99

 

[Pho3nix]

Hi Guys,

Now if I for example wanted to only select sales that occured on the weekend how would I do that? :\

 

[PsyWulf]

Ex: select * from table where salesdate >= '12/11/2011' AND salesdate < '14/11/2011' ?

If column has date type

 

[Pho3nix]

I think i have figured it out.

DATEPART(DW,saledate) IN (7,1)