Full Text Search Indexing For apostrophe

Question

Post reply

Full Text Search Indexing For apostrophe

deepzzzz

SSCrazy

Points: 2890
More actions
July 16, 2012 at 10:35 pm

#392757

[font="Arial"]Hi,
I have to use exact match in my application. If I am searching "Application's" it should display only those columns which contain value same as "Application's". But results include "application" also. I don't want to be like this. I have used the below query.
SELECT * FROM table_name WHERE CONTAINS(column_name, '"application''s"')
My SQL server version is SQL Server 2008 R2.
Please let me know if any details are required
Please help me.....:-)[/font]
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

subbu1 Hall of Fame Points: 3281 More actions · Answer 1

PLS TRY BELOW CODE.

DECLARE @T1 TABLE(ID INT,NAME VARCHAR(30))

INSERT INTO @T1(ID,NAME)

SELECT 1,'AA'

UNION

SELECT 2,'BB'

UNION

SELECT 3,'"Application''s"'

UNION

SELECT 4,'"Application"'

SELECT * FROM @T1

WHERE NAME LIKE '"Application%'

deepzzzz SSCrazy Points: 2890 More actions · Answer 2

[font="Arial"]Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.[/font]

Thanks

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

deepzzzz (7/16/2012)
[font="Arial"]Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.[/font]

Little correction in Subbareddy code will work.

DECLARE @T1 TABLE(ID INT,NAME VARCHAR(30))

INSERT INTO @T1(ID,NAME)

SELECT 1,'AA'

UNION

SELECT 2,'BB'

UNION

SELECT 3,'"Application''s"'

UNION

SELECT 4,'"Application"'

SELECT * FROM @T1

WHERE NAME LIKE '"Application''s%'

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

subbu1 Hall of Fame Points: 3281 More actions · Answer 4

subbu1

Hall of Fame

Points: 3281

July 16, 2012 at 11:39 pm

#1513389

can you tell me.

above query which output need.

subbu1 Hall of Fame Points: 3281 More actions · Answer 5

subbareddy542 (7/16/2012)
can you tell me.
above query thich output need.

rhythmk SSCertifiable Points: 7271 More actions · Answer 6

deepzzzz (7/16/2012)
[font="Arial"]Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.[/font]

Hi deepzzzz,

It is always better to provide sample INSERT statements and required output to avoid any confusion.

Well let us know if you get the solution and if not then provide the required details.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

deepzzzz SSCrazy Points: 2890 More actions · Answer 7

Just suppose I have table columns like this

1 Application

2 Application's

3 Foreign Application

4 Application's effect

So, if I am running query SELECT * FROM table_name WHERE CONTAINS(*, ' "Application''s" ')

I am putting " " for exact match. Double ' ' (Single quote) after application is used for representing ' inside " ".

So result of the above query should be,

2 Application's

4 Application's effect

Please let me know if you need any other explanations

Thanks

rhythmk SSCertifiable Points: 7271 More actions · Answer 8

deepzzzz (7/17/2012)
Just suppose I have table columns like this
1 Application
2 Application's
3 Foreign Application
4 Application's effect
So, if I am running query SELECT * FROM table_name WHERE CONTAINS(*, ' "Application''s" ')
I am putting " " for exact match. Double ' ' (Single quote) after application is used for representing ' inside " ".
So result of the above query should be,
2 Application's
4 Application's effect
Please let me know if you need any other explanations

So the above corrected subbareddy's query should work.

Putting again with your example.

DECLARE @T1 TABLE(ID INT,NAME VARCHAR(30))

INSERT INTO @T1(ID,NAME)

SELECT 1,'Application'

UNION

SELECT 2,'Application''s'

UNION

SELECT 3,'Foreign Application'

UNION

SELECT 4,'Application''s effect'

SELECT * FROM @T1

WHERE NAME LIKE 'Application''s%'

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

deepzzzz SSCrazy Points: 2890 More actions · Answer 9

The given example is working fine. But using contains in full text its not giving me the right answer

I have changed the query as below.

SELECT * FROM table_name WHERE CONTAINS(*, '"application''s%"')

This query is not working... 🙁

Thanks

rhythmk SSCertifiable Points: 7271 More actions · Answer 10

deepzzzz (7/17/2012)
The given example is working fine. But using contains in full text its not giving me the right answer
I have changed the query as below.
SELECT * FROM table_name WHERE CONTAINS(*, '"application''s%"')
This query is not working... 🙁

Sorry I don't have proper right to use full-text index on my tables .

But it seems there is some problem in your query like why you are using double quotes.

Please try to store the word in a varibale and then use that in CONTAINS.

e.g.

DECLARE @SearchWord nvarchar(30)

SET @SearchWord = 'Application''s'

SELECT * FROM TableName

WHERE CONTAINS(ColumnName, @SearchWord)

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

deepzzzz SSCrazy Points: 2890 More actions · Answer 11

This won't work for us.

Why I am using " " , means search for exact match in the table.

Thanks

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 12

deepzzzz (7/17/2012)
The given example is working fine. But using contains in full text its not giving me the right answer
I have changed the query as below.
SELECT * FROM table_name WHERE CONTAINS(*, '"application''s%"')
This query is not working... 🙁

Try this:

DECLARE @fullTextString VARCHAR(MAX)

SET @fullTextString = 'application' + CHAR(39) + 's'

SELECT * FROM table_name WHERE CONTAINS(*, @fullTextString)

Using the CHAR value instead of the apostrophe always works out fine for me since I'm rubbish at counting multiple '''''''s.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

deepzzzz SSCrazy Points: 2890 More actions · Answer 13

DECLARE @fullTextString VARCHAR(MAX)

SET @fullTextString = 'application' + CHAR(39) + 's'

SELECT * FROM table_name WHERE CONTAINS(*, @fullTextString)

It won't work. AS I am using exact match. In which, search word should be in double quotes in full text indexing

Thanks

deepzzzz SSCrazy Points: 2890 More actions · Answer 14

Full Text Indexing for exact match

If the search content contains apostrophe and the letter next to it is among (d,m,s) it won't work as it is specifying the date time variables in sql.

Thats why application's is not working...

Thanks for all the replies

Thanks