Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Use like in Integer Column

    hi guys! help please..Is it posible to use LIKE clause in a column with type integer? can you give an idea how? Thanks advance!

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    I assume that u want to use it for a wildcard search
    so if you type 1 you allso want the values 0.1 and 11 to 19 ect.

    But i can't see why you coudn't, have you tried it allready?? didn't think so

    Use it as following

    SELECT *
    FROM person
    WHERE (id LIKE '%1%')

    Id = INT value in db
    so you have to use it as a char value

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Marvels
    SELECT *
    FROM person
    WHERE (id LIKE '%1%')
    I assume you either have set Access to ANSI 92 compatible or you use SQL Server more - default wildcards for JET\ Access are '*'

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by daimous
    can you give an idea how?
    can you please give an example of an application where actually doing this has any meaning whatsoever?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2005
    Posts
    74
    can you please give an example of an application where actually doing this has any meaning whatsoever?
    I have a search form where it searches my students table by studentid,first name, middle name, etc...It just came to my mind that when the user choose to search by studentid i want the result to be all student where their studentid contains the search key. So, if i have 3 student id (198,199,200)
    and in my search key i have a value of %1% the search result should be student with studentid 198 and 199..Hope you get my point.

    where the result to be not exact of what he/she typed but i want a the result to be

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I really can't see how that is going to help anyone. If you don't know the exact ID of a student, they aren't going to know part of it either. They would search by the student's name.

    However, to answer your question:

    Code:
    SELECT SalespersonID FROM SALESPERSON WHERE SalespersonID Like "*1*"
    That would work, just substitute fields etc.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose you could convert the type to string and then use the like clause

    eg
    where cstr(myintegercolumn) like "*1*"

    but that would be both nasty and inefficient, and certainly shouldn't get any credits in a piece of homework

    nasty because you are converting from one datatype to another uneccesarily in a query
    inefficient because the the db engine will not be able to take advantage of any indexes, and will have to do a tablescan.

  8. #8
    Join Date
    Jul 2007
    Posts
    2
    Hi daimous

    I had a similar problem today. My accounting DB extract has an account field (integer), with Branch number at the start, followed by 5 digits to classify the account.

    So:
    131100 is Branch 1, Product Sales
    231100 is Branch 2, Product Sales
    261300 is Branch 2, Product Costs
    282100 is Branch 2, Interest Expense

    Now, if I want sales for each branch, I need to show all accounts where the last 5 digits of the account number are 31100. To achieve this, my query criteria is
    Code:
    Like "*" & [last 5 digits of Account]
    This will then prompt me to key the last 5 digits of the account, and will provide the sales for each branch.

    Hope this helps

    James

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Ideally, the branch number would be in a different field.

    You could also, in your case, perform a union of several different queries (one for each branch number) where you subtract 100000 (for the first Branch) 200000 (for the second branch) etc, where the result of the subtraction is 31100

    The union might be slower than casting the integer as a string, but I wouldn't think so...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by loquin
    The union might be slower than casting the integer as a string, but I wouldn't think so...
    Nice idea. If it is indexed then I would put money on your solution being faster.

  11. #11
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Branch question

    Quote Originally Posted by Wheres_the_Red
    Hi daimous

    So:
    131100 is Branch 1, Product Sales
    231100 is Branch 2, Product Sales
    261300 is Branch 2, Product Costs
    282100 is Branch 2, Interest Expense

    Code:
    Like "*" & [last 5 digits of Account]

    James
    Why not say
    Select * FROM TAble where right(5,FIELD) = 31100

    think that would be faster even so

  12. #12
    Join Date
    Jul 2007
    Posts
    2
    Sorry folks, I might have confused more than I helped. I wanted to provide a second "real world" situation where the question might be relevant, and how the integer CAN be used with a LIKE function.

    Originally Posted by loquin
    Ideally, the branch number would be in a different field.
    Agreed - but you get what you get!!!

    Originally Posted by loquin
    several different queries (one for each branch number)
    )
    Agreed - but I don't know how many branches there'll be next month.

    Select * FROM TAble where right(5,FIELD) = 31100
    Again a good idea, but the method I use allows me to specify up to 5 digits, for the summary.


    James

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Wheres_the_Red
    Agreed - but I don't know how many branches
    Code:
    SELECT    acc_no
            , acc_no - (100000 * MID(acc_f, 1, 1)) AS acc_classification
    FROM    dbo.the_table

Posting Permissions

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