Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Full Text Search Indexing For apostrophe Expand / Collapse
Author
Message
Posted Monday, July 16, 2012 10:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:52 AM
Points: 885, Visits: 257
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.....


Thanks
Post #1330488
Posted Monday, July 16, 2012 10:48 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:30 AM
Points: 880, Visits: 669
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%'
Post #1330492
Posted Monday, July 16, 2012 11:32 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:52 AM
Points: 885, Visits: 257
Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.

Thanks
Post #1330504
Posted Monday, July 16, 2012 11:37 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 548, Visits: 881
deepzzzz (7/16/2012)
Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.


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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1330505
Posted Monday, July 16, 2012 11:39 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:30 AM
Points: 880, Visits: 669
can you tell me.
above query which output need.
Post #1330507
Posted Monday, July 16, 2012 11:39 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:30 AM
Points: 880, Visits: 669
subbareddy542 (7/16/2012)
can you tell me.
above query thich output need.
Post #1330508
Posted Monday, July 16, 2012 11:49 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 548, Visits: 881
deepzzzz (7/16/2012)
Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.


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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1330513
Posted Tuesday, July 17, 2012 12:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:52 AM
Points: 885, Visits: 257
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
Post #1330523
Posted Tuesday, July 17, 2012 12:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 548, Visits: 881
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1330528
Posted Tuesday, July 17, 2012 12:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:52 AM
Points: 885, Visits: 257
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
Post #1330534
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse