select ids that begin with 5 6

[Zac5]

Hi,

I have a table with a range of id codes and I want to extract those that begin with 5. If this were a text field type I could do a select * from x where id like '5*' but since it's an integer is there a simple statement? I cannot use e.g. > 499 as there could be id codes in the tens, hundreds, thousands, etc

Thanks

 

[hmckillop]

select * from x where convert(varchar(10),id) like '5%'

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Jasen]

You could still use the LIKE statement, just CAST the field as varchar.
WHERE CAST(Id as varchar) LIKE '5%'

There's probably a more efficient way though.

 

[Zac5]

Thanks guys both are good solutions, was a little confused about why its '5%' and not '5*', I tested it and '5%' returns the results as expected but '5*' returns nothing, i'm sure there's an explanation somewhere. Thanks for your help.

 

[gmmastros]

% is the wildcard search in SQL Server. * is the wildcard search in Access (and possibly other databases).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Skittle]

I'm still a bit bemused over why STR() doesn't work.

select * from x where STR(id) like '5%'

Can anybody enlighten me?


Dazed and confused
(N+, MCAD)

 

[vongrunt]

STR() returns some leading blanks (left-padded 10-char string).

Just for the sake of circus art :

select * 
from blah
where 4 = power(10, log10(id) - floor(log10(id)))
-- and id > 0

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Skittle]

Dohh!!! Of course!!!

...Skittle skuttles back to the dark place under the network wracking in the computer room mumbling to himself...



Dazed and confused
(N+, MCAD)

 

[dbomrrsm]

select *
from x
where substring(id,1,1) = 5

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]