Wildcard search without LIKE? 1

[lespaul]

I am trying to write an SQL statement using a wildcard in the search criteria without using LIKE. Here's my dilemma:
The table structure (which is NOT MY DESIGN) has the Defendant's Name as ONE field (LAST FIRST M) - BAD CALL from my point of view, but that's what I have to deal with. So, now I am trying to do a search by name and I'd like to be able to do something like:

WHERE DEFNAM = 'SMIT*'

which would return only those rows that start with SMIT instead of using LIKE, which would also return

JONES SMITTY

Is there a way to do this? I am using an AS400 backend, programming in Delphi, but figured this was the best forum to get SQL syntax information.

Thanks for any insight!



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[rajeevnandanmishra]

Hi Leslie,

I think that you have to use like operator. But i am sure that you will get what you want by using the like operator properly.

e.g.

starting with 'smit' ---- WHERE defnam like 'smit%'
ending with 'smit' ---- WHERE defnam like '%smit'
containing 'smit' ---- WHERE defnam like '%smit%'

and some other permutation combination also.

Let us know if does not solve your query.


---
Raj

 

[SQLBill]

I don't know if Transact-SQL syntax will work for you, but that's what Microsoft SQL Server uses and what this forum is for.

Using = probably won't work. It'll look for SMIT*, which isn't what you want.

Use LIKE.


-SQLBill

 

[lespaul]

Thanks Raj, worked like a charm, I had always used LIKE with both '%something%' and didn't realize you could only use one!!

Thanks!
Leslie