working with patindex - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

working with patindex

Author

Topic

sureshprabhu
Starting Member

32 Posts

Posted - 2007-11-07 : 02:15:18

hi!
i have one datatable with some file's details like id, name, createdate. i wrote one storedprocedure to search in that table by filename using wildcard character. its also working perfectly. But the problem is i want to arrange the results in ascending order depended by matching level. If I search for 'file123' it should give results in one order by matching level, means - lets think the results are 'fil23', file123', 'file', 'file1'. the results should be in order like 'file123', 'fil23', 'file1', 'file'. the results of the sp which i used, are order by datecreated. Please help me to do this.

the storeprocedure i used -
"CREATE PROCEDURE [dbo].[SP_DYN_SearchinFiles_DynByWildcardsADMIN] @searchparam nvarchar(50)
AS
BEGIN
select
Id,
OriginalName,
ContentType,
FullName,
Department,
DateCreated,
Client,
username
from Files_Dyn where PATINDEX('%' + @SearchParam + '%', OriginalName) > 0

END"

Kristen
Test

22859 Posts

Posted - 2007-11-07 : 03:49:16

Do you only want to match (as per your example) on the number of characters that match, or would you prefer to match on "sounds like"?

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=A+better+Soundex

Kristen