problem with LIKE operation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem with LIKE operation

Author

Topic

klari
Starting Member

4 Posts

Posted - 2004-07-03 : 04:46:53

Dears,

I face a problem with sql SELECT statement in my VBProject. I use ADO connection.

I want select from a field with datatype Memo of MSAccess using ADO connection.

My Code is:
<blue>
Public con As ADODB.Connection
Public rst As ADODB.Recordset
Public com As ADODB.Command

Public Sub MyConnection()
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
Set com = New ADODB.Command
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data"_
"Source=MyDb.mdb;Persist Security Info=False"
End Sub

con.open
rst.open "select * from Person where Story LIKE '%lif%'", con, adOpenKeyset
rst.close
con.close
</blue>

Here actually I want to select all records with words contains the letter "lif".
But my recordset returns with records with words contains the letter "lif" togather and also returns with records with words doesn't contatin the letters "lif" togather. Really only 5 records are there with requested statement but it shows 8 records.
I think my recordset returns all other records in which the letters "lif" are included but not togather.

So anybody can forward a solution for it. I want only select those contanin the words "lif" togather.

With hopes and regards,

KlariMol

Kristen
Test

22859 Posts

Posted - 2004-07-03 : 07:46:43

'%lif%' will fid the letters "lif" anywhere within the field. The letters must be together, exact as you provide them, so I'm not suit sure exactly what the problem is you are needing to solve. (Perhaps post the 8 results you are getting ...)

If you want Whole Words, and not where the search text is embedded within a wordm and if your "Story" column is NOT a Text column you could use
WHERE ' ' + Story + ' ' LIKE '% lif %'
and if you need to catch various other word delimiters, such as period and comma, you could do
WHERE ' ' + Story + ' ' LIKE '% lif[ .,;:]%'

Kristen

klari
Starting Member

4 Posts

Posted - 2004-07-06 : 09:26:22

Special thanks for MR. Kristen

Dear Sir,

Really my problem is that when I select the records
with words that conatin letters "lif" togather the
recordset also show the records with words that
conatin letters "lif" but not togather.

For Example: The recordset returns records with the
word such as (lifestyle, life, enlifement, lift,
uplift) these are truth. But it also returns records
with words such as (solaikafe, elseif, kerlalianforce,
maleshichform etc.) these are false. In first groups
my condition is truth and these words contain the
letters "lif" togather, but in second group my
conditon is false. In these group words conatin the
letters "lif" but not togather, there are other
letters between these words.

The same problem I face in my project for a bank when
I type some letters in a txtbox I want to list in a
MsFlexGrid near by the text boxes all the branches of
the bank begening with the letters I typed.
For Example I type CHAN, this time the recrodset list
in FlexGrid the branches such as (Changuvetty,
Changaramkulam, Changanassery, Chanmanoor etc) these
are truth, and also list the branches such as
(Chiyyanoor, Chenamangalam, Cholankunnu, Calhatinam
etc) these are false.

In short my recordset list all the record with the
word that conatin my criteria letters either it is
togahter or is not togather.

So how can I solve this problem.

KlariMol

klari

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-06 : 09:33:32

Well, how about doing what was asked and posting some example data to show us that this is really happening??

klari
Starting Member

4 Posts

Posted - 2004-07-06 : 10:39:01

Thanks RickD

I don't know really what is required from my side. In my previous post I have put my two expereinces with examples from my two projects. I think you have got an idea on my problem really waht is. If there facility two send files attached I will send my project for your reference.

With regards,
KlariMol

klari

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-06 : 10:41:58

Access database as back-end ? Doesn't that use * as the wildcard rather than % ?

klari
Starting Member

4 Posts

Posted - 2004-07-06 : 11:11:02

Thansk for JasonGoff,

I use both Access and MS SQL 2000 server.

In my first project back end is Access and in second bank project back end is MS SQL 2000 server. And the problem is in two projects.

And I have read that in ADO data connection it is used (%) instead of (*) as the wildcard even if the back end is MS Access. When I used (*) the result was error.

Please help me

KlariMol

klari

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 20:58:55

I'm really struggling to understand how you are getting these incorrect mataches, because I can't see how SQL will deliver them. Here's what I get:


SELECT MyWord
FROM
(
SELECT	[MyWord] = 'lifestyle'
UNION ALL SELECT 'life'
UNION ALL SELECT 'enlifement'
UNION ALL SELECT 'lift'
UNION ALL SELECT 'uplift'
-- Rest should fail
UNION ALL SELECT 'solaikafe'
UNION ALL SELECT 'elseif'
UNION ALL SELECT 'kerlalianforce'
UNION ALL SELECT 'maleshichform'
) X
WHERE MyWord like '%lif%'

MyWord         
-------------- 
lifestyle
life
enlifement
lift
uplift

(5 row(s) affected)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources