SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question sql syntax problem

    I am having problems getting my sql syntax correct. This is what I have and it doesn't work:

    SELECT id, name as AName FROM artists WHERE Mid(name, 1, 1) Not Between Asc('A') And Asc('z') ORDER BY name

    There error I get is Expected End of Statement

    I need to return all records where name doesn't start with a letter. This sql statement is querying an Access database via asp.

    Can anyone tell me how it should be written?

    Thanks!

  2. #2
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Feb 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:

    SELECT id, name AS AName
    FROM artists
    WHERE name BETWEEN 'A%' AND 'Z%'
    ORDER BY name

    if case sensitivity if a prob youll have to do something like this:

    SELECT id, name AS AName
    FROM artists
    WHERE (name BETWEEN 'A%' AND 'Z%')
    OR (name BETWEEN 'a%' and 'z%')
    ORDER BY name

    let me know if this works.
    Last edited by -TheDarkEye-; Jan 27, 2002 at 02:34.

  3. #3
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DarkEye,

    the code that you supplied seems like it will return all the records that START with a letter. Will it?

    Couldn't you do "WHERE Name NOT BETWEEN 'A%' AND 'Z%'" ?

    Just a suggestion, seems like in theory it should work.

    'Till next time..
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try the following.

    Code:
    SELECT 	ID,
    	Name As [AName]
    FROM 	Artists
    WHERE 	LEFT(Name,1) Like '[!a-z]'
    ORDER BY Name;

  5. #5
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Feb 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ACK! Thx for catching that.

  6. #6
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anytime. Just caught my eye.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  7. #7
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Shane. That worked but I modified it to use Mid(Name,1,1). I thought with Left you specified what position to start with and it would go to the end from there. With Mid I specify where to start and how many characters I want.

    The not in A-z worked great!


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
  •