SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    UK
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sercing by the first number?

    Hello

    I have a list if VARCHARS, like this:

    1
    1A
    C2
    C1
    C10
    10
    21
    D11

    Can anyone say how I can select the items that begin with the number one? So I get:

    1
    1A
    C1

    but none of the others. SELECT * FROM table WHERE thing LIKE '%1%' won't work as that will pick C10, 21, and D11 as well

    Thanks

    Karl.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    C1 doesn't begin with the number 1?

    To get the ones that start with 1, use '1%' instead of '%1%'

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    UK
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    C1 doesn't begin with the number 1?

    To get the ones that start with 1, use '1%' instead of '%1%'
    Sorry, I wasn't clear. I want C1 to be included in the results, but not C10.

    Karl.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT str
      FROM strings
     WHERE SUBSTRING(str 
            FROM LEAST(NULLIF(POSITION('0' IN str),0)
                      ,NULLIF(POSITION('1' IN str),0)
                      ,NULLIF(POSITION('2' IN str),0)
                      ,NULLIF(POSITION('3' IN str),0)
                      ,NULLIF(POSITION('4' IN str),0)
                      ,NULLIF(POSITION('5' IN str),0)
                      ,NULLIF(POSITION('6' IN str),0)
                      ,NULLIF(POSITION('7' IN str),0)
                      ,NULLIF(POSITION('8' IN str),0)
                      ,NULLIF(POSITION('9' IN str),0))
            FOR 1 ) = '1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    So you want only those lines where '1' has no number in front or behind? I think you'll have to use a regex, but I can't tell you how.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    omg i just saw your clarification

    your requirement said "starts with 1" so why not C10?

    and is 10 to be returned or not?

    perhaps you ought to clarify the real situation you are working on

    p.s. yes i tested my solution on your initial data and requirement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    guido, the regex is actually easy

    but i want to hear the real requirements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    UK
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    omg i just saw your clarification

    your requirement said "starts with 1" so why not C10?

    and is 10 to be returned or not?

    perhaps you ought to clarify the real situation you are working on

    p.s. yes i tested my solution on your initial data and requirement
    I'm working on a bus timetable hack. So when someone is searching for the no 4 bus, they won't be wanting results for the 44, 48, 84 routes. However some buses have a leading character: C4, H4 etc, So I want those listed as well.

    Thanks

    Karl.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    WHERE (thing = '1' OR
           thing LIKE '1%' OR
           thing LIKE '%1'
          )

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    WHERE thing LIKE '%1'
    there are no suffixes

    and of course substitute the bus number (e.g. 44) in place of the 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    UK
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the responses, but neither of those worked how I wanted. I have been able to store the search along with the results, so a repeat search gets the same results.

    Karl.

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by karl101 View Post
    Thanks for the responses, but neither of those worked how I wanted. I have been able to store the search along with the results, so a repeat search gets the same results.

    Karl.
    You're right, my last solution is just your OP solution split in 3. Since you want the busline with optionally the non numeric pre- and suffixes, regex is the way to go.


Bookmarks

Posting Permissions

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