String pattren searchign using Full Text Seach Technique

Question

Post reply

String pattren searchign using Full Text Seach Technique

wkkhan82

SSC Rookie

Points: 29
More actions
November 25, 2010 at 7:50 am

#104008

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 [ohn] then how will it return (‘John’ , ‘Johny’, 'Dohny’).
I am using WHERE CONTAINS(Name, ‘%ohn%’) but it not returning any record, Actually I have to implement pattern matching search, mean system have to search all possible matched results.
So how can we implement FULL TEXT SEARCHING techniques, it can be done via LIKE operator but I have to search single name from 20 to 30tables, and each tables have millions of records, so LIKE takes too many time.
So please suggest me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

patelmohamad SSCommitted Points: 1873 More actions · Answer 1

Try with this add underscore in your search

Name like '_ohn%'

Patel Mohamad

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

post some sample data along with your query ..then you will get some faster results.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

see this link also http://www.sqlservercentral.com/Forums/Topic1026649-145-1.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

wkkhan82 SSC Rookie Points: 29 More actions · Answer 4

@patelmohamad : Name like '_ohn%' is not working. I am using following query

Me using : SELECT * FROM tblNames WHERE CONTAINS(Name, '*ohn*') -> No result returns

Your Suggestion : SELECT * FROM tblNames WHERE CONTAINS(Name, '_ohn%') -> No result returns

@Bhuvnesh : Me talking about string pattren searching, not comma separated names. Mean if user enter string ['ohn'] then my query has to return all possible matches which contains 'ohn'.

i.e. (‘John’ , ‘Johny’, 'Dohny’)

patelmohamad SSCommitted Points: 1873 More actions · Answer 5

Try this

Use Northwind

SELECT * FROM [Product Sales for 1997]

WHERE ProductName LIKE '%_im%'

result set

CategoryName ProductName ProductSales

--------------- ---------------------------------------- ---------------------

Produce Manjimup Dried Apples 23550.0200

Confections Teatime Chocolate Biscuits 2339.9900

Grains/Cereals Wimmers gute Semmelknödel 7125.4700

(3 row(s) affected)

Patel Mohamad