sql like problem ( 5 Views )

1. I have a courseno field and I need extract all the 400 level courses, I have this sql statement but it fails
   
   some of the courseno look like this FIN450, FIN300
   
   

   Code:

   ---

   SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits
FROM Course
WHERE (((Course.CourseNo) Like "%4%"));

   ---

   
   
   (gülçin, Botswana)


2. Quote:

   Originally Posted by williamsba Had to get creative since MID isn't supported in SQL :)

   youre right, and it needs to be. so dethfire, does it work?
   
   
   (serdar, Oman)


3. Had to get creative since MID isn't supported in SQL :)
   
   
   (ferit, Micronesia, Federated States of)


4. Quote:

   Originally Posted by williamsba Ok this is the one :D This will only look at the 4th character in the value: Code: SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits FROM Course WHERE right(left(Course.CourseNo, 4), 1) = '4'

   wow, i never wouldve thought of doing that. good job will :tup:
   
   
   (elif, Belgium)


5. Ok this is the one :D This will only look at the 4th character in the value:
   
   

   Code:

   ---

   SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits
FROM Course
WHERE right(left(Course.CourseNo, 4), 1) = '4'

   ---

   
   
   (SELÝM , Viet Nam)


6. Code:

   ---

   SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits
FROM Course
WHERE LEFT(Course.CourseNo, 4) = 'FIN4'
OR LEFT(Course.CourseNo, 4) = 'ABC4'
OR LEFT(Course.CourseNo, 4) = 'DEF4'

   ---

   
   
   (kutay, Samoa)


7. I don't think he's getting errors, it's just not pulling back any results. true or false Dethfire?
   
   
   (emre, Uruguay)


8. Quote:

   Originally Posted by williamsba How about: Code: SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits FROM Course WHERE LEFT(Course.CourseNo, 4) = 'FIN4'

   
   Close but there other three letter abbreviated course numbers
   
   
   (murat, French Polynesia)


9. Quote:

   Originally Posted by williamsba How about: Code: SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits FROM Course WHERE LEFT(Course.CourseNo, 4) = 'FIN4'

   thats exactly what i was talking about. if you still have an error after that, you should check the syntax in your select and from statements for misspellings and stuff like that
   
   
   (güldane, United Kingdom)


10. How about:
    
    

    Code:

    ---

    SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits
FROM Course
WHERE LEFT(Course.CourseNo, 4) = 'FIN4'

    ---

    
    
    (ayaz, Central African Republic)


11. Quote:

    Originally Posted by r937 what database is this? if the percent signs don't work it must be access LIKE '*4*' for access

    i use percents signs in access every day, ive never had that problem. but there really must be a better way to do what hes wanting, with the code that is there he would also bring back fin340, which he doesnt want. i dont know how to do it in sql, i would probably try bringing back the recordset like it is here and then trying to isolate it more using your programming language, but there ought to be a way to do it in sql.
    
    
    (tuðba, Netherlands Antilles)


12. what database is this?
    
    if the percent signs don't work it must be access
    
    LIKE '*4*' for access
    
    
    (elif, United Arab Emirates)


13. Quote:

    Originally Posted by williamsba Code: SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits FROM Course WHERE Course.CourseNo LIKE '%4%'

    still fails, doesn't find matches
    
    
    (yasin, Guinea-Bissau)


14. Code:

    ---

    SELECT Course.CourseNo, Course.CrsDesc, Course.CrsUnits
FROM Course
WHERE Course.CourseNo LIKE '%4%'

    ---

    
    
    (cihan , Marshall Islands)


15. Quote:

    Originally Posted by williamsba Had to get creative since MID isn't supported in SQL :)

    of course it is
    
    in standard sql, it is the SUBSTRING function
    
    each database has its own version of this function
    
    in sql server, SUBSTRING
    
    in access, MID
    
    in mysql, you have a panoply of choices:
    
    SUBSTRING(str,pos,len)
    SUBSTRING(str FROM pos FOR len)
    MID(str,pos,len)
    SUBSTRING(str,pos)
    SUBSTRING(str FROM pos)
    
    in access, i would always opt for MID(str,4,1) instead of RIGHT(LEFT(str,4),1)
    
    but no doubt about it, RIGHT(LEFT(str,4),1) is clever
    
    ;)
    
    
    (eray, Sudan)

Related Topics ... (or search in 1.720.883 topics !)