December 22, 2014 at 8:36 am
Hello all, I was wondering if someone can help me with some code here. My goal is that from SSRS there will be a single parameter text box that a user can enter either a Patients ID or they can enter Last Name then a comma the First Name to do a search. Currently the query works just fine for the PatientID portion but I am getting hung up on the Name search. It works part of the time but not others. I have supplied a small amount of DDL along with the query and also some test values that work and some that do not. Any guidance would be appreciated. Thanks in advance!
CREATE TABLE #Test (PatientID INT, PatientFirstName VARCHAR (100), PatientLastName VARCHAR (100))
INSERT INTO #Test VALUES (1, 'Thomas', 'Smith'), (2, 'SQL', 'Susie') , (3, 'Tony', 'Tiger'), (4, 'Sara', 'Bellum') , (5, 'Sponge Bob' , 'Squarepants')
DECLARE @PatientSearch VARCHAR (500)= 'Tiger, Tony' -- These work, 'Tig, Ton' ,'Tig, To','T, T' ,'Ti, T' ,'Tiger, Tony' But these don't, 'Tige, To','Tiger, To','Tig, Tony'
--If numeric use set to patientID and use patient id filter.
IF ISNUMERIC(@PatientSearch) =1
BEGIN
DECLARE @vPatientID VARCHAR (10)
SELECT @vPatientID = @PatientSearch
SELECT *
FROM #Test t
WHERE t.PatientID = @vPatientID
END
ELSE
--If string look for comma to find first and last name
BEGIN
DECLARE @vPatientFirstName VARCHAR (100)
DECLARE @vPatientLastName VARCHAR (100)
SELECT @vPatientFirstName = (RIGHT((@PatientSearch), charindex(',', (@PatientSearch)) -1))
SELECT @vPatientLastName = (LEFT((@PatientSearch), charindex(',', (@PatientSearch)) -1))
SELECT TOP (10) * FROM #Test t
WHERE (t.PatientFirstName LIKE RTRIM(LTRIM(@vPatientFirstName + '%')) AND t.PatientLastName LIKE (@vPatientLastName + '%'))
ORDER BY t.PatientLastName, t.PatientFirstName
END
DROP TABLE #Test
***SQL born on date Spring 2013:-)
December 22, 2014 at 12:05 pm
Change the statement for @vPatientFirstName to
SELECT @vPatientFirstName = (RIGHT((@PatientSearch), LEN(@PatientSearch) - charindex(',', (@PatientSearch)) -1))
December 22, 2014 at 1:07 pm
Thanks that did it! Now to understand what was going on.:-D
***SQL born on date Spring 2013:-)
December 22, 2014 at 1:23 pm
CHARINDEX returns the first position of a given character, counted from the left side.
The sample data that return data have a similar pattern: the length of the last name is equal to the length of the first name or one character more.
With your original approach the code will return the same number of characters from the right side as counted from the left side.
This lead to either the FirstName itself (if the length of both is identical) or to the name with a leading blank (the one after the comma).
Due to the RTRIM(LTRIM()) this leading blank is removed and the code works. Accidentally, more or less...
The change I made: The position of the comma is subtracted from the overall length of the string. The result is used to return the remaining number of character from the right side of the string.
December 22, 2014 at 1:26 pm
Thanks so much for the explanation the pattern you mentioned is something I noticed but until you explained it I did not know why it was occurring. I really want to get better with strings! Thanks again for your help sir!!:-D
***SQL born on date Spring 2013:-)
December 26, 2014 at 3:31 am
Hi Lutz ,
Thanks for the simple way you explain your use of CHARINDEX and RTRIM. I think I have discovered some subtleties of the manipulation of strings in SQL Server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply