problems in Like

hi there,
im facing a wierdo problem

i had data in a foxpro database, i import it to a sqlserver 2000 database with collation Arabic_CI_AS , cause the data is arabic and english

now the data, i can view it and its correct

but the problem is that when i use the like statement to do the search operation

i use this sql:
Select * from Customers where CustomerName like N'%searchText%'

the customerName is of type nvarchar

now if i use one word then it gives me result, but if i use in the search two words (between them space) it dont work, or if i put one word then space it dont work

and in the same time if i copy the customerName from the table and search for it, it gives me the result, even if it was one word or two words or even three!!!

i belive there is a problem in the data, there is something in data i dont know what it is, but this is what it think, i changed the collation, but sometimes it got worst than that

SO IS THERE ANY ONE THAT CAN HELP!!!!!!

:)

thanks in advanceHi,
can you offer more details?
or can you paste your codes in?|||well yeah sure i can offer more details

This is the stored Procedure

CREATE PROCEDURE SP_Customers_GetAllRec

(
@.Searchfield as nvarchar(300)

)

AS
/* SET NOCOUNT ON */
select Customers.ID,CustomerName,RankID,StartDate,EndDate,Address,Username,Password,Email,EnteredBy,CustomerDiscount
from Customers inner join customerDiscount on Customers.CustomerDiscountID = CustomerDiscount.ID
where customername like '%'+@.SearchField+'%' order by CustomerName asc

RETURN
GO

and i send the @.Searchfield as an example N'Super' , this for an example

i tried to write the sql in many forms...but nothing worked!!!

anyone can help?|||Hi,
just to confirm with you, you are using SQL Server 2000?
the query for your statment is something like this format:
SELECT id FROM customer WHERE name like '%ab cd%'
while the query for variables is something like this format:
SELECT id FROM customer WHERE name like '%'+@.testing+'%'

what i dont understand is this :
@.Searchfield as an example N'Super'
if you are searching for the word 'Super', @.Searchfield 's value should be 'Super'
but if you are looking for the word " N'Super' ", i dont think sqlserver can do it because it will take the ' as a quote, thus making your select statment like this :
SELECT id FROM customer WHERE name like '%'N' Super''%'
which obviouslly leads to an error.

in my previous projects what i did was i converted the values ' to ", so that sqlserver will not mistake the quotes.
but of course it needs the assumptions that your data in that field shouldn't have any "s
and that you'll need some extra coding before you call your stored procedure.

Sorry if it didn't help you|||... the N is they way for SQL-Server to treat a string as Unicode instead of ASCII - the N would not be inserted in the LIKE clause.|||:: now if i use one word then it gives me result, but if i use in the search two words
:: (between them space) it dont work, or if i put one word then space it dont work

Ok, example string: "One Two Three".

if you want to find any one of the words "One", "Two", Three" you have to do one LIKE clause per word you try to find and combine them with OR, example:

SELECT
 *
FROM
 Table
WHERE
 column LIKE '%ONE%'
 OR column LIKE '%TWO%'
 OR column LIKE '%THREE%'

... is that what you want to do? If not please post some sample data and search strings that work and that don't.

Hth,

Moon|||Hi All,

Thank you moon, but what the problem is ,its not that i want to find every word, the problem is when i try to find the 2 or 3 words together like :

select * from Table where column like '%one two three%'

all together, i dont know why there is space i cant find the word i want

this is the problem

thank you again|||Further thoughts are: are you sure you have a " " char in you strings? Since you did convert the data from a non-SQL server database, maybe you do not have a real space char that seperates the words but another char that will be displayed as a space (but having different keycode) and thus the query fails.

For testing you could try if one of the following searches will return records:

LIKE '%one%two%three%'
>> match the words in this order with any number of any other chars between them
LIKE '%one_two_three%'
>> match the words in this order with exactly one other chars between them

If so, then you have to find out which char is seperating them, by anylyzing the search result using the SQL-Server string functions for unicode.

Hth,

Moon|||I found some code in Books Online in the section explaining the ASCII function that you might be able to use to help you discover which characters are in your fields exactly. I adapted it slightly - you will need to change the line I have indicated in red:

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @.position int, @.string char(15)
-- Initialize the variables.
SET @.position = 1
-- change this line of code so that one record from your table is selected
SELECT @.string = somefieldcontainingspaces FROM sometable WHERE someuniquefield = someuniquevalue
WHILE @.position <= DATALENGTH(@.string)
 BEGIN
 SELECT ASCII(SUBSTRING(@.string, @.position, 1)),
 CHAR(ASCII(SUBSTRING(@.string, @.position, 1)))
 SET @.position = @.position + 1
 END
SET NOCOUNT OFF

Normal spaces are ASCII character 32. Which character do you have in your field?

Terri|||hii

sorry i was in an urgent vacation

thank you tmorton i will check it and reply soon