Tricky query 1

[chrissparkle]

I have a varchar field which has 88 numbers in it.. it might look like this for instance: 111112221121221222 etc. Each number will either be a 1 or a 2. What I need to do is build a query where I pass in a number, let's say "20", and I need to bring back results where the 20th number in that field is a "2". So if someone has a 1 for the 20th item in the field it won't bring back their record.

Is this even possible I wonder?

 

[mrdenny]

Use substring in your where clause. Keep in mind that this will not use an index when doing this.

select *
from table
where substring(col1, 20, 1) = '2'

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbkris]

hi,

another approach (this was done just to retain the index).

try this:

select *
from table
where col like '[12][12][12]1%'

the last 1 is the position that you want to search (in this case its 4th position)...

Known is handfull, Unknown is worldfull