SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    where topic has exact 8 in 12th position

    Code:
    data in myTable
    
    (id) topic
    (1)  the article8s (about health) 
    (2)  the article15s (my deam)
    (3)  the article38s (What can you say about ~)
    (4)  the article82s (How to make money)
    (5)  the article103s (my lovely baby)
    (6)  the article923s (NULL in your mind)
    (7)  the article1273s (Happy new year)
    I have data in myTable like the above.

    I have a variable its value is 8.

    I like to find the exact id (1) of "article8s (about health)" with the only number 8 in topic columns.

    The following is one of my trial code.

    Code:
    code
    select id
    from myTable
    where
    right(left(topic,12),1)=8
    
    result
    
    (1)
    (4)
    
    target result
    
    (1)
    And I like to get the target result above, by using only 8.
    Last edited by dotJoon; Dec 30, 2008 at 06:15.

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could try ANDing it with another condition to check for the 13th column.
    Tony Mathew Thomas
    modVPS
    HostingZOOM Inc.
    POWER. SPEED. RELIABILITY.

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hztonyt View Post
    You could try ANDing it with another condition to check for the 13th column.
    Code:
    right(left(topic,12),1)=8 
    and 
    right(left(topic,13),1)='s'
    Although "s" is not "the only 8," the code above gets what I want since "s" is fixed character.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Although "s" is not "the only 8,"
    Are there any other possibilities??
    Tony Mathew Thomas
    modVPS
    HostingZOOM Inc.
    POWER. SPEED. RELIABILITY.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nothing like a joon thread to get one wondering!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
     WHERE SUBSTRING(topic FROM 12 FOR 1) = '8'
       AND NOT ( topic REGEXP '[012345679]' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •