query based on 2nd to last number??

[stubnski]

Hi, I know this has to be easy, but I just can't get it right. I need to base a query on the 2nd to last number in a field so if I wanted all numbers with a four 2nd to last I would get

2345
6441

Any help would be greatly appreciated

 

[earthandfire]

You could try something like:

Select FieldName 
From TableName
Where Substring(Convert(varchar(50), FieldName),
	              Len(Convert(varchar(50), FieldName)) -1, 1) = '4'

A bit long winded, but it allows for values of any number of digits (up to 50 digits that is)

Hope this helps.

 

[zeuz]

if it's a numeric field you're working with it's likely easier and possibly even a lot faster to do it mathamatically.

select FieldName from TableName where (FieldName % 100 - FieldName %10)/10 = 4

 

[MDXer]

Select FieldName
From TableName
Where Substring(Reverse(FieldName),2,1) = '4'

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[stubnski]

Thanks for the help you guys

 

[stubnski]

found that this works as well

table_name.field_name Like '%4_'

 

[vongrunt]

If you really hate someone (aka: how to make code unreadable ) and column is an integer, try:

WHERE FieldName/10%10 = 4

------
"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)