Is there any way to use LIKe operator with interger values

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sunilgiri
    New Member
    • Feb 2007
    • 2

    Is there any way to use LIKe operator with interger values

    Hi,
    I have an integer field with 10 digit values.I want to allow the user to search with starting numbers say 5 digits.

    if the search is for 123 all the numbers starting with 123 should be displayed. Is there any operator similar to LIKE for this purpose?
  • r035198x
    MVP
    • Sep 2006
    • 13225

    #2
    Originally posted by sunilgiri
    Hi,
    I have an integer field with 10 digit values.I want to allow the user to search with starting numbers say 5 digits.

    if the search is for 123 all the numbers starting with 123 should be displayed. Is there any operator similar to LIKE for this purpose?
    I've never heard of it. Maybe your design is wrong then. Do you really must have that column as an integer?

    Comment

    • letmatros
      New Member
      • Feb 2007
      • 2

      #3
      Maybe I don't get the problem, but if your values are always 10 digits, then you could do something where you fetch all records with
      1230000000<= x <=1239999999

      Comment

      • r035198x
        MVP
        • Sep 2006
        • 13225

        #4
        Originally posted by letmatros
        Maybe I don't get the problem, but if your values are always 10 digits, then you could do something where you fetch all records with
        1230000000<= x <=1239999999
        I think they want 123, 1234, 123009 e.t.c to be returned as well.

        Comment

        • deangc
          New Member
          • Feb 2007
          • 1

          #5
          Originally posted by r035198x
          I think they want 123, 1234, 123009 e.t.c to be returned as well.
          I think you're probably stuck converting the integer value to a varchar and operating on it as a string.

          For example,
          WHERE CAST(colname as VARCHAR(10)) LIKE '123%'

          Note that depending on your design, this could have performance implications.

          Comment

          • rgarrep
            New Member
            • Mar 2007
            • 1

            #6
            WHERE CAST(EMP_id AS VARCHAR(8)) LIKE '24%' really works fine.

            Comment

            • r035198x
              MVP
              • Sep 2006
              • 13225

              #7
              Originally posted by rgarrep
              WHERE CAST(EMP_id AS VARCHAR(8)) LIKE '24%' really works fine.
              Indeed. It's a good trick for this.

              Comment

              Working...