Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL FULLTEXT

Posted on 2011-10-27
17
Medium Priority
?
369 Views
Last Modified: 2012-05-12
I'm trying to write a SQL statement that I can use to search for a word in a table. I cant seem to add FULLTEXT as its a shared server and wont let me. I have played around with LIKE statements, but it doesnt seem to work and was wondering if anyone else has any ideas...basically i have a form feild where the user types in a word and it searches the database for all matches. I cant  seem to work out how to do this.

Any help would be great
0
Comment
Question by:hayward03
  • 5
  • 5
  • 4
  • +1
17 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37040273
Can you post the query you have been trying?
0
 

Author Comment

by:hayward03
ID: 37043369
SELECT main_news_id, main_news_date, main_news_heading FROM ink_main_news WHERE (main_news_heading like '%" & search & "%' OR main_news_article like '%" & search & "%')
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37045377
An equivalent SQL Server query would look like this:

Declare @search varchar(50)

Set @search = 'some value'

SELECT  main_news_id, main_news_date, main_news_heading
FROM  ink_main_news
WHERE (main_news_headinglike '%' + @search +  '%'
OR  main_news_article like '%' + @search + '%')
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Expert Comment

by:GenerLopez
ID: 37050604
Probably you have  a upper/lowercase issue.
So, you must equal the text case to do the query properly.

Also there is a much simpler solution:

select main_news_id , main_news_date,main_news_heading from ink_main_news 
 WHERE (LOWER(main_news_date || main_news_heading) like ('%' || LOWER(search) || '%'))

Open in new window


Thus, you can concatenate all the fields to have only one comparation, then using lowered text on both sides.
I understand that one match on any field selects the record
 FYI:  "||" stands for "concatenate" for SQL92 standard, you must adapt to your DB flavour

I've tested it and works (on SQL92)

Hope this help
Gener
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37050663
>>I've tested it and works (on SQL92)<<
Unfortunately, the author appears to be using T-SQL and the solution as is does not compile using SQL Server.
0
 
LVL 1

Expert Comment

by:GenerLopez
ID: 37052820
Fortunately there is a very, very small difference.

Ok, sustitute || by + and it works on Transact-SQL

A real Transact SQL query against a real database:

SELECT [name]
      ,[address]
  FROM [MARDOM_DESARROLLO].[SISTEMA].[Client]
  where lower([name] + [address]) like ('%'+LOWER('AlMa')+'%')
GO

Open in new window



Mixing on code lower and uppercase characters

Aaaaand ... the result is... (wait a moment):

ALMACENES ORIENTAL <tab>
CORP. ZONA FRANCA  PALMAREJO <tab>
WILDA M. SURAN <tab> D/ BONAIRE#386,ALMAROSA 2DA
WF AUTO IMPORT C X A <tab> CALLE 23 # 1A ALMA ROSA 2DA
TECNOLOGIA YIRE, CXA. <tab> CALLE PROYECTO #1 ALMA ROSA
TERESITA PUJOL <tab> ALMA ROSA


<tab> is the column separator between name and address

As you can see "AlMa" is selected in both fields, including partial names, selecting the record if a match is found on any field

So the submitted query, translated to T.SQL is:

select main_news_id , main_news_date,main_news_heading from ink_main_news 
 WHERE (LOWER(main_news_date + main_news_heading) like ('%' + LOWER(search) + '%'))

Open in new window


I assumed that any SQL programmer can replace SQL concatenation operation with its equivalent for  their database engine, sorry
Also assumed that the query must select any combination of upper / lower case.
if capital letters are relevant, let me know

Thats all, folks

Hope this helps, at last
Gener


0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37053710
For what it's worth, using a function in the where clause is a very bad idea unless it it absolutely necessary.
0
 
LVL 1

Expert Comment

by:GenerLopez
ID: 37061922
cmangus,
I don't know how to override the upper/lowercase issue without using a function in where clause, unless it is known how the text is written at insert/update time, forcing uppercase or lowercase, and so on,  and the user typing the search word also does the correct text typing.
I think that the nature of this query, using "like" forces the server to read the entire table searching for matches, avoiding "per se" any use of indexes except if the whole text is previously indexed word by word.
Some server engines, such as FRONTBASE, have extensions to solve this problem, but I don't know if SQL server has this functionality inside.
So I propose a query in pure sql

Performance issues are related with number of records , number of fields, length and so on.
And we don't know this details.

Some feedback from hayward03 could bring us some light.

Also, if you have a better way to do this, let us know.

Regards

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37062323
SQL Server by default is case insensitive so it may be a moot point.
0
 
LVL 1

Accepted Solution

by:
GenerLopez earned 2000 total points
ID: 37062586
acperkins
you're right, well pointed.
Tested without lower and it still works


select main_news_id , main_news_date,main_news_heading from ink_main_news 
 WHERE (main_news_date + main_news_heading) like ('%' + (search) + '%')

Open in new window


but, seeing this, I've been playing around with the original query posted by hayward03

Tried it and it seems to work but changing "&" by "+" to concatenate like value and changing double quotes for single quotes in % (was  '%" & search & "%')

so, a query like this

SELECT main_news_id, main_news_date, main_news_heading FROM ink_main_news 
WHERE (main_news_heading like '%' + search + '%' OR main_news_article like '%' +  search + '%')

Open in new window


works fine

Perhaps the problem could be in the construction of the query (ASP?), probably in the way the search value is passed to the query.

Maybe hayward03 should check this and give us some feedback.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37063206
>>Maybe hayward03 should check this and give us some feedback.<<
I suspect hayward03 is MIA (that is a Vietnam term and roughly translates to "desaparecido en batalla")
 
0
 
LVL 1

Expert Comment

by:GenerLopez
ID: 37063517
acperkins,
Mmmm... so we are wasting time, isn't it?

I don't know how Experts Exchange could control this situation, but they should.

It's not so fair to post questions asking for advise and not following them appropiately.
By the way, this is my second post and is the second time that this happens to me.

I don't know if this is the "normal" situation, but 2 of 2 is more than I expected.
Regards



0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37064618
If you answer questions regularly you'll notice they can often go for quite some length of time without any action.  I have some questions I've participated in that are over a year old with no activity.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37065179
>>I don't know how Experts Exchange could control this situation, but they should.<<
It is unfortunately an on going problem and has been for the 10 years that I have participated:  Members who pay their monthly fee and do not followup even with a courtesy "I am looking into this".  It is more than frustrating when this happenes because of the lack of appreciation for the volunteers here.

EE has implemented some restrictions, such as you cannot ask any new questions, if you have abandoned (more than 21 days without activity) more than (I believe) 5 questions and they are still open.

In the end you have to be prepared to put up with this in return for the many times that a member thanks you for a solution to a problem that they have been trying to solve.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37065198
And I should have added, that eventually it will be up to another volunteer to clean up all abandoned questions by making the determination as to how the question should be closed.
0
 

Author Comment

by:hayward03
ID: 37068776
Hi All,

Thanks for all your posts, im going these now to have a look and will post feedback shortly....sorry for the late reply I have been tied up on another project and have only just been able to come back to this now.

Regards

Michael
0
 

Author Closing Comment

by:hayward03
ID: 37068796
I tweaked the solution to make it work for the purpose I needed if for.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question