October 4, 2010 at 5:00 am
Hi,
I will need to create a stored procedure to search for a person name.
Ex:
@name = 'John Smith'
I would like that this stored procedure returns persons called 'John Smith' and 'Smith John'.
@name2 = 'John Charles Smith'
I would like that this stored procedure returns persons called 'John Charles Smith' and 'Smith John Charles', etc..
So, i'm thinking to do something like this:
SELECT NAME FROM TALE
WHERE
NAME LIKE '%John%' AND NAME LIKE '%Smith%'
SELECT NAME FROM TALE
WHERE
NAME LIKE '%John%' AND NAME LIKE '%Charles%' AND NAME LIKE '%Smith%'
Since this name could be composed by several parts, how do i split the name in its parts and search from them dinamically?
Thanks
October 4, 2010 at 5:32 am
What about "J. Smith", "J Smith", "Smith, J" and "Smith, J."; what about "John C. Smith", "John C Smith", "J C Smith", "J. C. Smith".
The endless possibilities can make searching a single text string for a single whole name name extremely complicated. If two columns are used, one for family name (or surname), the other for forname, and we assume that the family name is always stated in full, then this type of search becomes a lot easier. Are there any other keys that you can use, such as postcode?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2010 at 5:33 am
as-534320 (10/4/2010)
I will need to create a stored procedure to search for a person name.Ex:
@name = 'John Smith'
I would like that this stored procedure returns persons called 'John Smith' and 'Smith John'.
@name2 = 'John Charles Smith'
I would like that this stored procedure returns persons called 'John Charles Smith' and 'Smith John Charles', etc..
So, i'm thinking to do something like this:
SELECT NAME FROM TALE
WHERE
NAME LIKE '%John%' AND NAME LIKE '%Smith%'
SELECT NAME FROM TALE
WHERE
NAME LIKE '%John%' AND NAME LIKE '%Charles%' AND NAME LIKE '%Smith%'
Since this name could be composed by several parts, how do i split the name in its parts and search from them dinamically?
Assuming the issue is the search argument I'll parse it looking for characters like colon and space. substring() and charindex() will come handy.
In regards to dealing with all the parts of a given name I see two alternatives; 1) write a query that will deal with four or five parts or, 2) create the select statement on the fly.
A final note would be to be sure capitalization is consistent, otherwise you may want to upper() everything.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 4, 2010 at 5:42 am
Depending on the business case, maybe a full-text search is an option, too...
Other than that I second Chris: you should consider to store the data in separate columns.
As a side note: Is it important to distinguish between first and last name? If so: How will you determine the first name of "John David"?
October 4, 2010 at 6:39 am
Chris,
Yes, there are other parameters, such as birthdate, number, but in this case i only want to search by name.
Thanks for remember the endless possibilities..
SSCommitted,
I've tried create the select statement using dynamic sql, but in a function it is not allowed..
How can i use substring() and charindex()? Can you point me a small example?
Yes, the capitalization is consistent. They are all lower, besides my example is different.
I already thought in separation of names in columns, but not yet implemented..
Thanks for your help.
Regards
October 4, 2010 at 7:17 am
as-534320 (10/4/2010)
How can i use substring() and charindex()? Can you point me a small example?
Sure thing.
Example below solves scenario where there is a first and last name separated by a space.
declare @string varchar(64)
declare @i integer
select @string = 'John Smith'
select @i = 1
select substring(@string,
@i,
(charindex(' ',@string,@i) - 1))
select @i = (charindex(' ',@string,@i) + 1)
select substring(@string,
@i,
(len(@string) - (@i-1)))
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 4, 2010 at 7:36 am
as-534320 (10/4/2010)
...Since this name could be composed by several parts, how do i split the name in its parts and search from them dinamically?...
This query shows how to split a string up into words, and assumes a maximum of 9 words in the string - which should be ok for names. Each word in the string becomes a value in a new column.
DROP TABLE #Tale
CREATE TABLE #Tale (Wholename VARCHAR(200))
INSERT INTO #Tale (Wholename)
SELECT 'John Smith' UNION ALL
SELECT 'Smith John' UNION ALL
SELECT 'John Charles Smith' UNION ALL
SELECT 'Smith John Charles'
SELECT s.*, x.*
FROM #Tale s -- up to 9 segments in 1 column
CROSS APPLY (
SELECT
MAX(CASE d.ColNo WHEN 1 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col1],
MAX(CASE d.ColNo WHEN 2 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col2],
MAX(CASE d.ColNo WHEN 3 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col3],
MAX(CASE d.ColNo WHEN 4 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col4],
MAX(CASE d.ColNo WHEN 5 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col5],
MAX(CASE d.ColNo WHEN 6 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col6],
MAX(CASE d.ColNo WHEN 7 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col7],
MAX(CASE d.ColNo WHEN 8 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col8],
MAX(CASE d.ColNo WHEN 9 THEN SUBSTRING(d.Wholename, d.b, d.e-d.b) END) AS [Col9]
FROM (
SELECT s.Wholename,
ColNo = ROW_NUMBER() OVER(ORDER BY n.n), -- string segment number
n.n AS b, -- delimiter at beginning of string
ISNULL(NULLIF(CHARINDEX(' ', s.Wholename, n.n+1), 0), LEN(s.Wholename)+1) AS e -- delimiter at end of string
FROM (SELECT TOP 200 [n] = ROW_NUMBER() OVER(ORDER BY [name]) FROM master.dbo.syscolumns) n -- generates 200 rows where n = 1 to 200
WHERE SUBSTRING(' ' + s.Wholename + ' ', n.n, 1) = ' '
AND n < LEN(s.Wholename)
) d
) x
It's not really possible to describe how this would best be used to address your issue without more information: you could set this up as a CTE and query from that, you could output results to a temp table, or you could use it directly as a derived table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2010 at 9:40 am
Thanks for your help.
Assuming that the user do not will search for 9 or more names of a patient i will use something like this:
SELECT
*
FROM
USERS
WHERE
NAME IS NOT NULL
AND (
NAME LIKE CASE WHEN @PART1 IS NULL THEN NAME ELSE @PART1 END
AND
NAME LIKE CASE WHEN @PART2 IS NULL THEN NAME ELSE @PART2 END
AND
NAME LIKE CASE WHEN @PART3 IS NULL THEN NAME ELSE @PART3 END
AND
NAME LIKE CASE WHEN @PART4 IS NULL THEN NAME ELSE @PART4 END
AND
NAME LIKE CASE WHEN @PART5 IS NULL THEN NAME ELSE @PART5 END
AND
NAME LIKE CASE WHEN @PART6 IS NULL THEN NAME ELSE @PART6 END
AND
NAME LIKE CASE WHEN @PART7 IS NULL THEN NAME ELSE @PART7 END
AND
NAME LIKE CASE WHEN @PART8 IS NULL THEN NAME ELSE @PART8 END
AND
NAME LIKE CASE WHEN @PART9 IS NULL THEN NAME ELSE @PART9 END
where each part is '%part_of_the_name%'.
October 8, 2010 at 3:47 am
I've found another solution using dynamic sql:
DECLARE @NAME VARCHAR(100)
SET @NAME = 'john charles smith'
declare @CMD VARCHAR(8000)
SET @CMD = ('
SELECT
*
FROM USERS
WHERE
SEARCH_NAME LIKE ''% '+replace(@NAME,' ',' %'' AND SEARCH_NAME LIKE ''% ')+' %''')
--print @cmd
EXEC(@CMD)
The query would look like this:
SELECT
*
FROM USERS
WHERE
SEARCH_NAME LIKE '% john %' AND SEARCH_NAME LIKE '% charles %' AND SEARCH_NAME LIKE '% smith %'
My SEARCH_NAME column stores the name in lower case without accents and with a space after and before SEARCH_NAME.
Comments are welcomed.
Regards.:-)
October 11, 2010 at 1:06 pm
It came become not very straight forward when you throw in the case of muti-part last names like Mc Donald or O Brien and Suffixes Like Sr, Jr or III.
Steve
October 11, 2010 at 3:30 pm
Full text search would probably be the best option.
October 11, 2010 at 3:37 pm
Hi,
Steve block,
Most of cases, the full name is fullfilled. Its about patient names. But i agree with you.
SSCommited,
I don't know how to work with full search.. Can you point me some article or good link about it?
Thanks.
Reagards 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply