This is giving me a headache 2

[dalec]

I'm trying to search an address within a field. For example when I search '4500' I get results, but, if I search '4500 L' or '4500 Lamont' it comes back with no results. Here's my code:

SQL = "select * from customer where charindex('" & Trim(UCase(Sch)) & "', rtrim(upper(adr)) ) > 0 order by adr;"
Set Cust = Adbs.Execute(SQL)

thanks in advance,
Dale

 

[vongrunt]

What is Sch and what is adr... and what is stored in customer table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

You could use the Like syntax.

Select * from Customer Where adr Like '4500 L%'

The percent symbol is your wildcard search in sql server (it's * in access). If you have a percent at the beginning and end of the like string, it will look anywhere within the field.

adr Like '%4500 L%'

If you want to ONLY search the beginning, don't include the first percent symbol.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should also mention that *usually* SQL Server searches are case in-sensitive, depending on your collation. So, you could *probably* remove all that upper case business. You'll need to test this to make sure.

Also, when running code in-line like you are, apostrophes can kill you. In your original example, you have "Trim(UCase(sch))". Ideally, you should be creating stored procedure in the database and using Command objects in your code. If you prefer not to do that, you should at least accomodate the apostrophes.

SQL = "select * from customer where charindex('" & Replace(Trim(UCase(Sch)), "'", "''") & "', rtrim(upper(adr)) ) > 0 order by adr;"
Set Cust = Adbs.Execute(SQL)

You can accomodate apostrophes in your data by replacing a single apostrophe with 2 consecutive apostrophes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dalec]

I'm thinking once I get this working I will create the stored procedure for it. using the % (beginning and ending) still produce the same results. Any ideas?

 

[dalec]

It has something to do with the space, when I tried an address with just a name like 'early' then 'early morning', early was found, early morning was not. (both exist).

 

[dalec]

You were right case is not an issue

 

[SQLDenis]

works for me
declare @C table (Searchstring varchar(500))
insert into @c
select 'sdsdearly morningsfdfdf'
union all
select 'sdsdearly'

select * from @c where Searchstring like '%early morning%'
select * from @c where Searchstring like '%early%'

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[gmmastros]

Is it possible that you have multiple spaces in the data?

ex. 'early[green]<space><space>[/green]morning'

Try this query to see if it works.

select * from Customer Where Replace(adr, '  ', ' ') Like '%early morning%'

Basically, 2 spaces are replaced with a single space for comparison purposes. Of course, if you have 10 spaces between early and morning, then you still have more problems. But give it a shot. See what happens.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Here is the answer use a regular expression
where Searchstring like '%[early +morning]%'

declare @C table (Searchstring varchar(500))
insert into @c
select 'sdsdearly morningsfdfdf'
union all
select 'sdsdearly'
union all
select 'sdsdearly morningsfdfdf'

select * from @c where Searchstring like '%[early +morning]%'

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[dalec]

thanks, I'll check it out in a minute and post back

 

[SQLDenis]

This is strange?
declare @C table (Searchstring varchar(500))
insert into @c
select 'sdsdearly morningsfdfdf'
union all
select 'sdsdearly'
union all
select 'sdsdearly morningsfdfdf'
-- correct
select * from @c where Searchstring like '%[early +][mo]%'
--should not return 'sdsdearly'
select * from @c where Searchstring like '%[early +][mor]%'

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[gmmastros]

Denis,

When i run your query (simply copy/paste to qa window), I get 3 results, but only 2 records have 'morning' in them. I know nothing about regular expressions (or sql server's implementation of them).

Why would the 2nd record be returned?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I know i just posted areply
correct
select * from @c where Searchstring like '%[early +][mo]%'
Incorrect returns the one without morning also?
select * from @c where Searchstring like '%[early +][mor]%'

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[dalec]

Thank you, it was from an early data import, there infact were 3 spaces between the address number and the actual street name. Is in always like that, (right in front of you then Duoh!!)

thanks again,
Dale