Hello, I wonder if you can help me please?I have a form in a VB app that will find people based on their forename and surname regardless of if they have a middle name.So if I type in 'Joe Bloggs' it will find 'Joe Bloggs', 'Fred Joe Bloggs', 'Joe Fred Bloggs' etcThe SQL I'm using to get this is:SELECT EmployeeNumber, FullName, EmailAddress, Manager, Forename, Surname, CHARINDEX(' ', @EEName) AS FPosition, RTRIM(SUBSTRING(@EEName, CHARINDEX(' ', @EEName) + 1, LEN(@EEName))) AS FSurname, SUBSTRING(@EEName, 1, CHARINDEX(' ', @EEName) - 1) AS FForenameFROM tbl_EmployeeDetailsWHERE (Forename LIKE N'%' + SUBSTRING(@EEName, 1, CHARINDEX(' ', @EEName) - 1) + N'%') AND (Forename LIKE N'%' + RTRIM(SUBSTRING(@EEName, CHARINDEX(' ', @EEName) + 1, LEN(@EEName))) + N'%') OR (Forename LIKE N'%' + SUBSTRING(@EEName, 1, CHARINDEX(' ', @EEName) - 1) + N'%') AND (Surname LIKE N'%' + RTRIM(SUBSTRING(@EEName, CHARINDEX(' ', @EEName) + 1, LEN(@EEName))) + N'%') OR (Forename LIKE N'%' + RTRIM(SUBSTRING(@EEName, CHARINDEX(' ', @EEName) + 1, LEN(@EEName))) + N'%') AND (Surname LIKE N'%' + SUBSTRING(@EEName, 1, CHARINDEX(' ', @EEName) - 1) + N'%') OR (Surname LIKE N'%' + SUBSTRING(@EEName, 1, CHARINDEX(' ', @EEName) - 1) + N'%') AND (Surname LIKE N'%' + RTRIM(SUBSTRING(@EEName, CHARINDEX(' ', @EEName) + 1, LEN(@EEName))) + N'%') OR (Forename = N'%' + @EEName + N'%') OR (Surname = N'%' + @EEName + N'%') However, if I simply type in 'Joe' this query won't work. If I type in 'Joe ' (With a space at the end) it will find all names with 'Joe' in them as I want.Obviously I know why it's happening - it's because the Substring function is splitting on a space, and if it doesn't find one it doesn't know what to do. The trouble is though I don't know how to fix it.I've narrowed down the problem to this line, but I don't know what I need to change my SQL to:SUBSTRING(@EEName, 1, CHARINDEX(' ', @EEName) - 1) AS FForename
Problem with SUBSTRING
Posted - 2011-09-27 : 09:12:59
