July 16, 2012 at 10:35 pm
[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
July 16, 2012 at 10:48 pm
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%'
July 16, 2012 at 11:32 pm
[font="Arial"]Its showing both Application with IDs 3 & 4. I want exact match for apostrophe.[/font]
Thanks
July 16, 2012 at 11:37 pm
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
🙂
July 16, 2012 at 11:39 pm
can you tell me.
above query which output need.
July 16, 2012 at 11:39 pm
subbareddy542 (7/16/2012)
can you tell me.above query thich output need.
July 16, 2012 at 11:49 pm
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
🙂
July 17, 2012 at 12:20 am
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
July 17, 2012 at 12:39 am
deepzzzz (7/17/2012)
Just suppose I have table columns like this1 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
🙂
July 17, 2012 at 12:45 am
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
July 17, 2012 at 1:31 am
deepzzzz (7/17/2012)
The given example is working fine. But using contains in full text its not giving me the right answerI 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
🙂
July 17, 2012 at 2:00 am
This won't work for us.
Why I am using " " , means search for exact match in the table.
Thanks
July 17, 2012 at 2:27 am
deepzzzz (7/17/2012)
The given example is working fine. But using contains in full text its not giving me the right answerI 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: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.
July 17, 2012 at 2:40 am
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
August 8, 2012 at 5:35 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply