need help with like statement...

[SpreadTooThin]

I have a column with data that looks like:

AB_XYZ
BB_123
123456

I need to get a list of a rows where this column's data has an _ as the 3rd character.

Select c from table where c like '__!_' escape '!';

Is that correct?

[Salvatore]

It would be easier to use a query like this:

SELECT c FROM `table` WHERE c LIKE '__\_';

You don't need the "ESCAPE" clause. In fact, I'd recommend you not use
it.

--
Blah blah bleh...
GCS/CM d(-)@>-- s+:- !a C++$ UBL++++$ L+$ W+++$ w M++ Y++ b++

[The Natural Philosopher]

Salvatore wrote:
> On 2012-10-17, SpreadTooThin <bjobr...@gmail.com> wrote:
>> I have a column with data that looks like:
>>
>> AB_XYZ
>> BB_123
>> 123456
>>
>> I need to get a list of a rows where this column's data has an _ as the 3rd character.
>>
>> Select c from table where c like '__!_' escape '!';
>>
>> Is that correct?
>
> It would be easier to use a query like this:
>
> SELECT c FROM `table` WHERE c LIKE '__\_';
>
> You don't need the "ESCAPE" clause. In fact, I'd recommend you not use
> it.
>

Better

SELECT c FROM `table` WHERE c LIKE '__\_%';

--
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

[Lennart Jonsson]

If the string you are looking for is exactly 6 chars you need to add
wildcards for the remaining three:

like '__!____' escape '!'

/Lennart

[Lennart Jonsson]

On 10/17/2012 09:08 PM, Salvatore wrote:
> On 2012-10-17, SpreadTooThin <bjobr...@gmail.com> wrote:
>> I have a column with data that looks like:
>>
>> AB_XYZ
>> BB_123
>> 123456
>>
>> I need to get a list of a rows where this column's data has an _ as the 3rd character.
>>
>> Select c from table where c like '__!_' escape '!';
>>
>> Is that correct?
>
> It would be easier to use a query like this:
>
> SELECT c FROM `table` WHERE c LIKE '__\_';
>
> You don't need the "ESCAPE" clause. In fact, I'd recommend you not use
> it.
>

Perhaps you should inform the OP that your recommendation is non
standard, and probably wont work with other DBMS?


/Lennart

[Brian Cryer]

"The Natural Philosopher" <t...@invalid.invalid> wrote in message
news:k5n1nc$1m9$1...@news.albasani.net...

> Salvatore wrote:
>> On 2012-10-17, SpreadTooThin <bjobr...@gmail.com> wrote:
>>> I have a column with data that looks like:
>>>
>>> AB_XYZ
>>> BB_123
>>> 123456
>>>
>>> I need to get a list of a rows where this column's data has an _ as the
>>> 3rd character.
>>>
>>> Select c from table where c like '__!_' escape '!';
>>>
>>> Is that correct?
>>
>> It would be easier to use a query like this:
>>
>> SELECT c FROM `table` WHERE c LIKE '__\_';
>>
>> You don't need the "ESCAPE" clause. In fact, I'd recommend you not use
>> it.
>>
> Better
>
> SELECT c FROM `table` WHERE c LIKE '__\_%';

Just be aware that this won't use any indexes, so performance for a large
table will be poor ... or (out of curiosity) can someone show me a way of
adding a suitable index?
--
Brian Cryer
www.cryer.co.uk/brian

[Scott Johnson]

This is not meant as a flame but I have seen replies to many other
threads mention or in most cases complain about 'other DBMS'
non-compliant code.

This tends to be a trend from those who are familiar with other DBMS
syntax, so I may be way off base.

Since the OP is posting in MYSQL and by the looks of the header, no
other, then MYSQL should be all that is needed?

I suppose it may be nice to have that mention, just thinking out loud I
suppose.

Scotty

[The Natural Philosopher]

Doesnt LIKE use indices* on 'c'' if 'c' is indexed?

Hmm. Not if the first char is a wildcard it seems.

So how would YOU do this search so as to use an index then?

I can't see it..



*note CORRECT spelling.

[Jerry Stuckle]

MySQL can't use an index on this column because the first two characters
can be anything. Indexes can't be created on just the third character,
for instance.

About the only way you could index the table would be to have another
column with just the third character of this column. Not a great
solution, but you could manage the column with triggers.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[Peter H. Coffin]

Isn't one. Can't sensibly index arbitrary characters, essentially. If
it's not arbitrary and you're always looking in the same character
position for some reason, you get to choose between periodically
building an extract column of just that third character that you care
about often and indexing the new column, or doing that once and creating
a trigger to mirror the third character into the new column on insert or
update, which then lets the indexer do it's thing. What's best will
depend on the circumstances.

--
84. I will not have captives of one sex guarded by members of the
opposite sex.
--Peter Anspach's list of things to do as an Evil Overlord

[Jerry Stuckle]

Scotty, I think it's a valid point. The user may be using MySQL now -
but there's always the possibility of changing to another database.
Coding to standards where possible is always better, IMHO.

And just because the op is using MySQL now doesn't mean he will be
forever. Learning to code to the standards now makes coding with
another database much easier.

Of course it's not always possible to code to the standards - sometimes
you need to use some features of the database. But knowing the
exceptions makes everything easier in the long run.

[Brian Cryer]

"The Natural Philosopher" <t...@invalid.invalid> wrote in message

news:k5otll$c13$1...@news.albasani.net...

> Brian Cryer wrote:
>> "The Natural Philosopher" <t...@invalid.invalid> wrote in message
>> news:k5n1nc$1m9$1...@news.albasani.net...
>>> Salvatore wrote:
>>>> On 2012-10-17, SpreadTooThin <bjobr...@gmail.com> wrote:
>>>>> I have a column with data that looks like:
>>>>>
>>>>> AB_XYZ
>>>>> BB_123
>>>>> 123456
>>>>>
>>>>> I need to get a list of a rows where this column's data has an _ as
>>>>> the 3rd character.
>>>>>
>>>>> Select c from table where c like '__!_' escape '!';
>>>>>
>>>>> Is that correct?
>>>>
>>>> It would be easier to use a query like this:
>>>>
>>>> SELECT c FROM `table` WHERE c LIKE '__\_';
>>>>
>>>> You don't need the "ESCAPE" clause. In fact, I'd recommend you not use
>>>> it.
>>>>
>>> Better
>>>
>>> SELECT c FROM `table` WHERE c LIKE '__\_%';
>>
>> Just be aware that this won't use any indexes, so performance for a large
>> table will be poor ... or (out of curiosity) can someone show me a way of
>> adding a suitable index?
>
>
> Doesnt LIKE use indices* on 'c'' if 'c' is indexed?
> Hmm. Not if the first char is a wildcard it seems.

<snip>
> *note CORRECT spelling.

OT: Interesting, not sure about that ("indexes" vs "indices"). My dictionary
has both. Google tells me that both are in use - although which is used
more seems to depend on which part of the world you are in - so where you
are, you are probably correct (and being a Brit I gather I should prefre
"indices"). Doing a search of the MySQL documentation and the dominate term
is "indexes" by far. But this is a moot point. What is reassuring is that I
wasn't missing a trick saying there it wouldn't use any indices/indexes.
--
Brian Cryer
http://www.cryer.co.uk/brian

[Jerry Stuckle]

On 10/18/2012 7:35 AM, Brian Cryer wrote:
>

> Just be aware that this won't use any indexes, so performance for a
> large table will be poor ... or (out of curiosity) can someone show me a
> way of adding a suitable index?

Brian,

Another thought here. Is there a reason for having to search like this?
Or is it because of a non-normalized database?

For instance, let's say the column is 'answers' and it contains all the
answers to a survey (i.e. AB_CDBCADD with the '_' indicating no answer).
This would be a non-normalized database, and fixing the design would
solve the problem.

OTOH, there are valid reasons for needing a search like this, even on a
properly normalized database. That could very well be the case here.

Just any time I'm looking for a character in a specific position I look
at the database design and see if it can (and should) be improved upon.

[Lennart Jonsson]

On 2012-10-18 14:40, Scott Johnson wrote:
[...]

>
> This is not meant as a flame but I have seen replies to many other
> threads mention or in most cases complain about 'other DBMS'
> non-compliant code.
>
> This tends to be a trend from those who are familiar with other DBMS
> syntax, so I may be way off base.
>
> Since the OP is posting in MYSQL and by the looks of the header, no
> other, then MYSQL should be all that is needed?
>
> I suppose it may be nice to have that mention, just thinking out loud I
> suppose.
>

There's absolutely nothing wrong in using proprietary construction, as
long as you are aware that it is just that. Many times you are forced to
because of lack of functionality, efficiency reasons etc.

What I question is a recommendation to use a proprietary construction
over a standard one, when there is no point in doing so. At least I did
not see a motivation for such a choice.

/Lennart

[Luuk]

what about doing:
select * from table where substr(c,3,1)='_';

or would that not work in other DBMS's too ?

[Scott Johnson]

Yes I can see that point. And the remark on this thread was a practical
one. I was just thinking of some that get down right dirty with the
premise if you don't code to be portable your a slacker (hope that does
not age me).

Thanks for the input
Scotty

[Scott Johnson]

No doubt it is best to know if there is indeed a 'standard' that could
be more portable.

I have just seen threads go sideways on this issue.

Thanks
Scotty

[The Natural Philosopher]

Scott Johnson wrote:

>
> Yes I can see that point. And the remark on this thread was a practical
> one. I was just thinking of some that get down right dirty with the
> premise if you don't code to be portable your a slacker (hope that does
> not age me).
>

Bullshit. It entirely depends on the application. If it is simply never
going to be needed to operate on a big RDBMS why not take advantage of
the platform you have?

[Scott Johnson]

On 10/18/2012 6:03 PM, The Natural Philosopher wrote:
> Scott Johnson wrote:
>
>>
>> Yes I can see that point. And the remark on this thread was a
>> practical one. I was just thinking of some that get down right dirty
>> with the premise if you don't code to be portable your a slacker (hope
>> that does not age me).
>>
>
> Bullshit. It entirely depends on the application. If it is simply never
> going to be needed to operate on a big RDBMS why not take advantage of
> the platform you have?
>
>
>

What part of what I wrote is BS.

I agreed with Jerry that if you can perform the same
function/performance using a cross platform method/strategy, there would
be no harm is pointing it out.

[Jerry Stuckle]

Scott, according to TNP, it's bullshit because you agree with me.

[The Natural Philosopher]

Scott Johnson wrote:
> On 10/18/2012 6:03 PM, The Natural Philosopher wrote:
>> Scott Johnson wrote:
>>
>>>
>>> Yes I can see that point. And the remark on this thread was a
>>> practical one. I was just thinking of some that get down right dirty
>>> with the premise if you don't code to be portable your a slacker (hope
>>> that does not age me).
>>>
>>
>> Bullshit. It entirely depends on the application. If it is simply never
>> going to be needed to operate on a big RDBMS why not take advantage of
>> the platform you have?
>>
>>
>>
>
> What part of what I wrote is BS.
>
"if you don't code to be portable your a slacker"

(and should have read "if you don't code to be portable, you're a slacker.")

Assuming that you meant:

"What part of what I wrote is BS?"

with a question mark.

I hate these syntax errors, don't you?

Pure slackness. Wait till you learn to program a computer...

[Brian Cryer]

"Jerry Stuckle" <jstu...@attglobal.net> wrote in message
news:k5p82m$i58$1...@dont-email.me...

> On 10/18/2012 7:35 AM, Brian Cryer wrote:
>>
>> Just be aware that this won't use any indexes, so performance for a
>> large table will be poor ... or (out of curiosity) can someone show me a
>> way of adding a suitable index?
>
> Brian,
>
> Another thought here. Is there a reason for having to search like this?
> Or is it because of a non-normalized database?

Good question, one which hopefully the OP will answer?

> For instance, let's say the column is 'answers' and it contains all the
> answers to a survey (i.e. AB_CDBCADD with the '_' indicating no answer).
> This would be a non-normalized database, and fixing the design would solve
> the problem.
>
> OTOH, there are valid reasons for needing a search like this, even on a
> properly normalized database. That could very well be the case here.
>
> Just any time I'm looking for a character in a specific position I look at
> the database design and see if it can (and should) be improved upon.

Good points, I'm in agreement with you.

Sadly it doesn't look like the OP has responded to any replies in this
thread, so we may never learn the reason why.

[herman...@invalid.be.invalid]

Proprietary constructs are always at the whim of the owner (constructor) of
the software. They can change whenever the owner deems it necessary. And
that has happened in the past. And probably MySQL has a good record in
this, but that's no guarantee.

if this concerns a "mission critical" piece of software, I would recommend
to put up a big red flag in the program, so you remember that an issue might
arisee there.

Herman Viaene

--
Veel mensen danken hun goed geweten aan hun slecht geheugen. (G. Bomans)

Lots of people owe their good conscience to their bad memory (G. Bomans)

[Scott Johnson]

On 10/19/2012 1:55 AM, The Natural Philosopher wrote:
> Scott Johnson wrote:
>> On 10/18/2012 6:03 PM, The Natural Philosopher wrote:
>>> Scott Johnson wrote:
>>>
>>>>
>>>> Yes I can see that point. And the remark on this thread was a
>>>> practical one. I was just thinking of some that get down right dirty
>>>> with the premise if you don't code to be portable your a slacker (hope
>>>> that does not age me).
>>>>
>>>
>>> Bullshit. It entirely depends on the application. If it is simply never
>>> going to be needed to operate on a big RDBMS why not take advantage of
>>> the platform you have?
>>>
>>>
>>>
>>
>> What part of what I wrote is BS.
>>
> "if you don't code to be portable your a slacker"
> (and should have read "if you don't code to be portable, you're a
> slacker.")
>
> Assuming that you meant:
>
> "What part of what I wrote is BS?"
>
> with a question mark.
>
> I hate these syntax errors, don't you?
>
> Pure slackness. Wait till you learn to program a computer...
>
>

Well you did answer an internal question I had about you.

Thanks