Re: Select statement pulling up too many rows

I'm not sure I understand your question, but I'll take a stab at it:

Try:

select data_id from table where to_number(substr(data_id,7,2)) = 12;

the 'substr(...)' would need to be modified for numbers with more than or less than 2 digits.

-Eric

danny hindes wrote:

> I am trying to develop a select statement that will pull up the one and
> only record that exists in some data where an id has been
> programatically indexed by one, but in a situation where the data wasn't
> zero filled. In other words, if I
>
> select data_id from table where data_id like '%\&\@|^1^12%';
>
> then I get the records
>
> &@|^1^12
> &@|^1^123
> &@|^1^1277
>
> You get the drift. It would have been nice if the application had zero
> filled the index, so that 00012 would have exactly found only one row.
> But then of course, I'd like Chrysler to replace my Lemon, and we'll
> just see how that goes....
>
> This may have an obvious solution, but I am fairly new at all this,
> being a former super-user who jumped the fence and became an analyst.
>
> Is there a single select statement that will in all cases just find the
> single row I ask it to find? Note that all the data in this column
> start out with the &@|^1^ characters, and that there is data after the
> index in most of the data that is returned.
>
> Thanks,
>
> Danny Hindes
> danny.hindes_at_SW.boeing.com
Received on Tue May 19 1998 - 13:07:11 CDT