February 23, 2010 at 2:42 pm
SELECT Mid([Num],5,2) & "-" & Mid([Num],7,2) & "-" & Right([Num],2) AS MRN, Left([PatName],(InStr([PatName],",")-1)) AS LastName, Right([PatName],(Len([PatName])-InStr([PatName],","))-1) AS FirstName, TABLE1.DOB
FROM TABLE1
GROUP BY Mid([Num],5,2) & "-" & Mid([Num],7,2) & "-" & Right([Num],2), Left([PatName],(InStr([PatName],",")-1)), Right([PatName],(Len([PatName])-InStr([PatName],","))-1), TABLE1.DOB;
The above query is chopping of the first letter of the lastname column all of a sudden. Can you let me know if the sql is right ?
Thanks
February 23, 2010 at 3:43 pm
Hi sqlserver12345,
Are you sure that you're not losing the first character from the forename (FirstName field) as opposed to the surname (LastName field)? It'd be easy to get mixed up, as with that format of name (surname, forname) the last name comes first and the first name comes last! 😀
If you are, then I think that you just need to lose the "-1" in the following:
Right([PatName],(Len([PatName])-InStr([PatName],","))-1)
If you use "Smith,John" as an example then...
The length of the string is 10.
The position of the comma is 6.
Taking one from the other gives you the 4 characters you need to return from the right of the string, i.e. "John". Deducting one more gives you just 3 characters and "ohn".
Now if you were trying to return the characters before the comma using Left, "-1" would be perfectly correct. You wouldn't believe how many times I've made exactly the same mistake...
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply