Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: help on extracting only with three capital letters from the field

    Hi Everybody

    I have this situation where I need to extract values from a field that satisfies this criteria...

    ONLY THREE LETTERS AND
    ALL IN CAPITAL LETTERS

    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT someField
    FROM   someTable
    WHERE  someField LIKE '[A-Z][A-Z][A-Z]'
    EDIT: Wait.... No...
    EDIT EDIT: This will work depending on collation settings - see post #5
    Last edited by gvee; 02-12-08 at 10:20.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select field
      from daTable
     where len(field) = 3
       and field = upper(field)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmm, I think there is a database setting about compare methods...
    Code:
    DECLARE @x char(3)
    
    SET @x = 'abc'
    
    IF @x LIKE '[A-Z][A-Z][A-Z]' BEGIN
      PRINT 'E.g. 1'
    END
    
    IF @x LIKE '[a-z][a-z][a-z]' BEGIN
      PRINT 'E.g. 2'
    END
    
    IF @x = Upper(@x) BEGIN
      PRINT 'E.g. 3'
    END
    
    IF Convert(binary, @x) = Convert(binary, Upper(@x)) BEGIN
      PRINT 'E.g. 4'
    END
    
    IF @x = Upper(@x) BEGIN
      PRINT 'E.g. 5'
    END
    Results
    Code:
    E.g. 1
    E.g. 2
    E.g. 3
    E.g. 5
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You using a case sensitive collation there OP? Coz if not then Rudy & George's queries need a wee adjustment...

    Actually Rudy - what does your do with 'A2C'?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots - see my example 4


    clunky but correct...
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - clunky ain't the half of it

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - the "Compare Method" you are thinking of might be to do with Access\ VBA. It is actually called something like MethodCompare..... In SQL you have many different collations - apart from other things, some are Case Sensitive (CS) and some are Case Insensitive (CI). You will see those two letters in many of the collation names - that's what they mean.

  9. #9
    Join Date
    Aug 2006
    Posts
    87

    yes got it

    thanks the solution of rudy went thru and yes if in case there's a number in the field would it still work?

    again thanks

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No. I like George's solution, me.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    No. I like George's solution, me.
    Point to George

    I think that takes the scores up to
    George 1 - Rudy 100,000,000
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - you are now on -4. You went down to -5 that time you said mean and hurtful things to me.

    The day you were Cast Out.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have no idea what you're talking about...



    damn this archivist!
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    me too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So I think with a 2:1 margin, I am on 1 point
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •