Wildcard search for single character only

#10 (**permalink**) 28-Jul-2006, 08:25 AM

Jamie Collins wrote:
> sdg8481 wrote:
>
>>i want it to
>>contain a W and no other letter (apart from more W's or :'s)
>
>
> Try:
>
> my_col NOT LIKE '*[!W:]*'
>
> There is no need to explicitly handle null values because of the
> inherent behaviour of NULL values in SQL DDL i.e. an 'unknown' value
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11913-wildcard-search-for-single-character-only.html
> cannot be known to fail a validation rule, therefore it is allowed to
> pass.
>
> While we are on the subject of wildcard characters, you should
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11913
> explicitly handle both 'ANSI' and 'non-ANSI' flavours of (multi-)
> character wildcard, otherwise you validation rule may be thwarted
> simply by switching from DAO to ADO e.g.
>
> my_col NOT LIKE '%[!W:]%' AND my_col NOT LIKE '*[!W:]*'
>
> Jamie.
>
> --
>

Jamie,

I think your basic idea is great, even elegant, but the second example I
tried -- "::" -- didn't work correctly.

James A. Fortune
MPAPoster@FortuneJames.com

#11 (**permalink**) 28-Jul-2006, 08:25 AM

James A. Fortune wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11913
> > Try:
> >
> > my_col NOT LIKE '*[!W:]*'
> >
> > you should
> > explicitly handle both 'ANSI' and 'non-ANSI' flavours of (multi-)
> > character wildcard
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11913
> >
> > my_col NOT LIKE '%[!W:]%' AND my_col NOT LIKE '*[!W:]*'
>
> I think your basic idea is great, even elegant, but the second example I
> tried -- "::" -- didn't work correctly.

I didn't use "::" in my second example.

Jamie.

--

#12 (**permalink**) 28-Jul-2006, 08:27 AM

Perhaps the best question would be "Why do you have such a situation that
would require this kind of query?" Maybe you are approaching it wrong to
start with, or maybe the database design needs altered as to not created
such a situation.

I believe that, generally speaking, if something is very difficult and messy
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11913
to do,...it is probably being done wrong to start with.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

"sdg8481" wrote in message
news:497AB029-C21A-4FE1-A2F3-CDB694286B89@microsoft.com...
> Thanks James, but i can't appear to get this to work
>
> "James A. Fortune" wrote:
>
> > sdg8481 wrote:
> > > Hi,
> > >
> > > I have a field wihich contain a variety of letters, examples below;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11913
> > >
> > > W:YMW
> > > :W
> > > W:WWW
> > > Y:Y9
> > > 9:W9
> > > W:W
> > > Y:YW
> > >
> > > ETC....
> > >
> > > i need to pick out only those rows that have a W, no matter how many
W's,
> > > but must have no other letter, but can have the : character. How can
this be
> > > include into a IF statement, like below
> > >
> > > If([Column Header] = ???? , "E", "I"
> > >
> > > Hope you can help Thanks
> >
> > If you are using A2K or greater, try something like (untested):
> >
> > If InStr(Nz(MyFieldOrVariable), ""), "W", 1) > 0 And
> > Len(Replace(Replace(Nz(MyFieldOrVariable, ""), ":", "W"), "W", "")) = 0
> > Then ...
> >
> > The field or variable must contain a "W" and then after changing ":"'s
> > to "W"'s and changing any resulting "W"'s to empty strings there can be
> > nothing left. I think Ofer read your post a little too quickly and
> > missed the part about no other letter.
> >
> > James A. Fortune
> > MPAPoster@FortuneJames.com
> >