FuLLText Search in SQL server

Question

1.00/5 (1 vote)

See more:

I Have a loads and loads of data in my table column on which FullTextSearch is to be performed.For suppose in my column i have stjohns,saintanns,st.Peterburg and if i pass "st." then it should return all the three values..Please help me regarding this, Even i tried noise words to eliminate '.', but it is not working.

Thanks in advance
Lohieth

Posted 7-Oct-13 3:26am

lohithrao

Add a Solution

Comments

Richard C Bishop 7-Oct-13 10:05am

What does your query look like? Have you tried "WHERE [column] like '%st%'"?

lohithrao 7-Oct-13 10:38am

yeah %st.%, it is returning only st. names

Richard C Bishop 7-Oct-13 10:53am

Ok, what would you expect then? You need to add an "and" to your where clause that checks for both values.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard C Bishop · Answer 1 · 2013-10-07T04:57:00

Solution 1

You would need something like this:

Select * From [table] where [column] like '%st%' and [column] like '%saint%'

Posted 7-Oct-13 4:57am

Richard C Bishop

Updated 7-Oct-13 5:20am

v3

Padam Agrawal · Answer 2 · 2013-10-07T05:48:00

Solution 2

Please go thorough,

SQL

-- stjohns, saintanns, st.Peterburg
-- and if u pass "st." then 1 reocrds will display -> st.Peterburg
-- and if u pass "st" then 2 reocrds will display -> stjohns, st.Peterburg
-- and if u pass "s" then 3 reocrds will display -> stjohns, saintanns, st.Peterburg

Declare @val varchar(100) = 's'

SELECT *
FROM YourTableName
WHERE YourColumnName Like '%+ @Val +%'

Have a great day !!!

Posted 7-Oct-13 5:48am

Padam Agrawal

Comments

Richard C Bishop 7-Oct-13 11:53am

That will get every column that just has an "s" in it. That is not what the OP needs.

lohithrao 8-Oct-13 5:06am

Thanks for Reply Padam Agarwal but that doesn't solve my requirement, as when i will pass

Declare @val varchar(100) = 'st.'
Then it should return all the 3 names

Padam Agrawal 8-Oct-13 9:33am

Hey, I am not getting what you want, as logically can you explain when you pass 'st.' then how it can be returned all of three? what the logical behind it can you describe then i can help you.

on other hand instead of 'st.' you can just pass 's' for all 3 records.

I can help you out only when you describe logic what you exactly want.

Thanks,

lohithrao 15-Oct-13 4:44am

Thanks for the reply PadamAgarwal ji and sorry for the delay.
In my data base i have
1)stJohns
2)st.Peterburg
3)saintAnns
In my Auto-complete box i have an option of sending 3 characters to search from database.I wanted to search 'stJohns' but the user passing 'st.' to get stJohns, As he is thinking that stjohns might be saved as st.johns. This is context.

Padam Agrawal 15-Oct-13 4:48am

Hmm, Instead of doing adhoc solution you can update the record as st.johns