Explanation of complex SQL 2

[oaklandar]

The below takes a field (called name) with first name and last name and sorts it by alpabetical order by last name.

name Field values
John Smith
Richard Dawson
Carl Anderson

Result of query:
Carl Anderson
Richard Dawson
John Smith

SELECT name
FROM TableOne
ORDER BY Mid(name, InStr(1, name, Chr(32)) + 1)

Please explain what is going on in this sql?
Does InStr cut up the field into arrays?
firstname is 0?
Space is 1?
Lastname is 2?

What is Chr(32) + 1?

What is the mid and function doing??

 

[hneal98]

Mid(name, InStr(1, name, Chr(32)) + 1)

MID is a function that retrieves for a string in the MIDdle of another string. Instr is a function that searches for a string starting (in this case,) the first position in the string from the string in the name field. CHR(32) is a space. CHR is a function that returns a character representation of a number from the ASCII table, which you can look up on the internet. The +1 means to add one the the number returned by the Instr function. Here is an example:

Name = Carl Anderson

Mid(name, InStr(1, name, Chr(32)) + 1) would return Anderson.

InStr(1, name, CHR(32)) + 1 would return 6. The function states, "Starting at position 1, look into the [name] field, look for a space (chr(32)), and add 1 to that number.

Hopefully that clears it up. Let me know if there is something that is confusing.

 

[willir]

CHR(32) is space character

InStr(1, name, Chr(32))
InStr searchs for the location of the "space" character
InStr(1, "John Smith", Chr(32)) returns 5

Mid(name, InStr(1, name, Chr(32)) + 1)
Parses the text string "name" starting one charcter left of the space character
Mid("John Smith", 5 + 1) returns "S"

So basically, you are ordering by the first character right of the the first space. Works fine for "John Smith" but would give less than desireable results for...
John Smith
Sue Ellen Smith

Still a pretty novell approach form handling a field with a first + last name.

Richard

 

[oaklandar]

Thanks!!!