Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Wildcard search for single character only

Discussion in 'Information Technology' started by sdg8481, Jul 28, 2006.

  1. sdg8481

    sdg8481
    Expand Collapse
    Guest

    Hi,

    I have a field wihich contain a variety of letters, examples below;

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Using a query with Like.

    Select * From TableName Where FieldName Like "*" & [Please write a letter] &
    "*"

    Or in another way, in the query under the field write the criteria

    Like "*" & [Please write a letter] & "*"


    The user will be prompt with a message to enter a letter
    --
    Good Luck
    BS"D


    "sdg8481" wrote:

    > Hi,
    >
    > I have a field wihich contain a variety of letters, examples below;
    >
    > 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
     
  4. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    sdg8481 wrote:
    > Hi,
    >
    > I have a field wihich contain a variety of letters, examples below;
    >
    > 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
     
  5. sdg8481

    sdg8481
    Expand Collapse
    Guest

    Thanks Ofer, But this would find all entries which contain a W, i want it to
    contain a W and no other letter (apart from more W's or :'s).

    "Ofer Cohen" wrote:

    > Using a query with Like.
    >
    > Select * From TableName Where FieldName Like "*" & [Please write a letter] &
    > "*"
    >
    > Or in another way, in the query under the field write the criteria
    >
    > Like "*" & [Please write a letter] & "*"
    >
    >
    > The user will be prompt with a message to enter a letter
    > --
    > Good Luck
    > BS"D
    >
    >
    > "sdg8481" wrote:
    >
    > > Hi,
    > >
    > > I have a field wihich contain a variety of letters, examples below;
    > >
    > > 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
     
  6. sdg8481

    sdg8481
    Expand Collapse
    Guest

    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;
    > >
    > > 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
    >
     
  7. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try this
    1. Copy this function to a module

    Function CheckForLetter(FieldName As String, LetterToCheck As String) As
    Boolean
    Dim I As Integer
    CheckForLetter = True
    FieldName = Replace(Replace(FieldName,":","")," ","")
    For I = 1 To Len(FieldName)
    If Mid(FieldName, I, 1) <> LetterToCheck Then
    CheckForLetter = False
    Exit Function
    End If
    Next I
    End Function


    2. Run this SQL using the function as a criteria
    SELECT TableName.*
    FROM TableName
    WHERE CheckForLetter([FieldName],[Select a letter])=True

    --
    Good Luck
    BS"D


    "sdg8481" wrote:

    > Thanks Ofer, But this would find all entries which contain a W, i want it to
    > contain a W and no other letter (apart from more W's or :'s).
    >
    > "Ofer Cohen" wrote:
    >
    > > Using a query with Like.
    > >
    > > Select * From TableName Where FieldName Like "*" & [Please write a letter] &
    > > "*"
    > >
    > > Or in another way, in the query under the field write the criteria
    > >
    > > Like "*" & [Please write a letter] & "*"
    > >
    > >
    > > The user will be prompt with a message to enter a letter
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "sdg8481" wrote:
    > >
    > > > Hi,
    > > >
    > > > I have a field wihich contain a variety of letters, examples below;
    > > >
    > > > 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
     
  8. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Another way, without the function
    Select * From TableName Where
    Len(Replace(Replace([FieldName], [Select a letter], ""), ":", "")) > 0

    --
    Good Luck
    BS"D


    "sdg8481" wrote:

    > Thanks Ofer, But this would find all entries which contain a W, i want it to
    > contain a W and no other letter (apart from more W's or :'s).
    >
    > "Ofer Cohen" wrote:
    >
    > > Using a query with Like.
    > >
    > > Select * From TableName Where FieldName Like "*" & [Please write a letter] &
    > > "*"
    > >
    > > Or in another way, in the query under the field write the criteria
    > >
    > > Like "*" & [Please write a letter] & "*"
    > >
    > >
    > > The user will be prompt with a message to enter a letter
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "sdg8481" wrote:
    > >
    > > > Hi,
    > > >
    > > > I have a field wihich contain a variety of letters, examples below;
    > > >
    > > > 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
     
  9. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    sdg8481 wrote:
    > 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;
    >>>
    >>>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
    >>


    How are you trying to use it? Are you actually trying to use IIf in a
    query instead of If? Are you trying to use this in VBA code. I think
    we're getting close, but a little more information might help us get
    there more quickly. Also, try Ofer's latest attempt. If that works,
    one of the three of us can add the InStr part in conjunction with using
    the same query parameter prompt technique.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    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
    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
    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.

    --
     
  11. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    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
    > 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
    > 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
     
  12. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    James A. Fortune wrote:
    > > Try:
    > >
    > > my_col NOT LIKE '*[!W:]*'
    > >
    > > you should
    > > explicitly handle both 'ANSI' and 'non-ANSI' flavours of (multi-)
    > > character wildcard
    > >
    > > 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.

    --
     
  13. Phillip Windell

    Phillip Windell
    Expand Collapse
    Guest

    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
    to do,...it is probably being done wrong to start with.

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


    "sdg8481" <sdg8481@discussions.microsoft.com> 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;
    > > >
    > > > 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
    > >
     

Share This Page