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.
| Author |
Topic |
|
wkkhan82
Starting Member
3 Posts |
 Posted - 2010-11-22 : 11:01:03
|
| How are you.I need help in FULL TEXT SEARCHING in SQL Server. Actually I have table which have names(‘John’, ‘Shaun’, ‘Shan’, ‘Shane’, ‘Johny’, ‘Dohny’), and I want to search text [Shane] then how will it return (‘Shane’ , ‘Shan’, ‘Shaun’).I am using CONTAINS(Name, ‘Shane’) but it only returning ‘Shane’, it should return all 3similar names.And It only searches Pre-fix characters when I used *, but I want to search similar words from that table.So how can we implement FULL TEXT SEARCHING techniques, it can ma done via LIKE but I have to search single name from 20 to 30tables, and each tables have thousands of records, so LIKE takes too many time.So please suggest me.Regards,Muhammad Waseem |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-11-23 : 05:31:57
|
| CREATE PROC [dbo].[USP_NAME_SEARCH]( @NAME VARCHAR(100), @FORMATNAME VARCHAR(50))ASBEGIN /*********DECLARATIION OF LOCAL VARIABELS****************************/ DECLARE @ABBREVCONDITION VARCHAR(50), @NAMECONDITION VARCHAR(50), @STRCONDITION VARCHAR(MAX), @SQL VARCHAR(MAX), @ITEMNAME VARCHAR(50) /*********DECLARATIION OF LOCAL VARIABELS****************************/ IF @FORMATNAME = '' SET @FORMATNAME = 'S' /*********FORMAT ****************************/ SELECT @NAMECONDITION = CASE @FORMATNAME WHEN 'S' THEN @NAME + '%' WHEN 'C' THEN '%' + @NAME+'%' WHEN 'E' THEN + '%' + @NAME ELSE '%' END /*********CHECING OF CONDITION ****************************/ IF @NAME ='' SET @STRCONDITION = ' NAME LIKE ' + '''' + @NAMECONDITION + '''' ELSE IF @NAME <>'' SET @STRCONDITION = ' NAME LIKE ' + '''' + @NAMECONDITION + '''' /*********EXECUTING OF DYNAMIC SQL****************************/ SET @SQL=' SELECT NAME from TABLE WHERE ' + @STRCONDITION + ' ' EXEC(@SQL)ENDuse this procedure by making changes as per your requirement.if u have any doubts ask me.ah |
 |
|
|
wkkhan82
Starting Member
3 Posts |
Posted - 2010-11-23 : 09:57:52
|
| Thanks Ahmed for you interest, but as i said above, I need solution other than LIKE operator. Actually i have millions of records in different tables, i practically implemented it via LIKE but from single table string patterns taking 0.7secs of execution..So I need efficient way for string pattern matching algorithm.Regards,Muhammad Waseem |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-23 : 09:59:51
|
| Are you trying to find something to replace SQL Server's Full Text Search capabilities? |
|
|
|
wkkhan82
Starting Member
3 Posts |
Posted - 2010-11-23 : 10:24:48
|
| No SQL Server's Full Text Search will be perfect, but i didn'd find how I can search similar words using Full Text Searching.It search Prefix characters(i.e. WHERE CONTAINS(NAME, 'Joh*') it searches John, Johny, Johney) but but how we can search 'Jeohn'?Regards,Muhammad Waseem |
|
|
|
|
|
|
|
|
Full Text Searching