Patrick/Norman,
Yup MS SQL 2008 server with a twist.
Background -
Tables are the database behind SAP Business ONE and cannot be changed neither can the data so I am using a view (in a separate database) to combine 3 tables (UNION) with various bits of postal address, contact information and account information.
This works well and is fast, as expected. It looks at about 20,000 customer entries each with at least 2 addresses (in a separate table) and multiple contact records (in a third table).
All good so far but I am trying to make it very easy for my users to search for a customer with a bit of any part of the address, name, account name, telephone number or email etc.
If I can specify the type of data they are searching for I don’t have a problem (if the search term has 4 or more numbers then it is a telephone number, if it has an ‘@’ then it is an email address) as I can narrow the search BUT if they just put in some text (street name, post code etc) I have to search on a number of key fields.
To make matters worse the original data is from 20 years f customer transactions so in the past some users put dashes in telephone numbers, some added commas in address fields etc. as SAP does not restrict the data entry too much.
This means I have to remove any non-alphanumeric characters whilst searching which is adding the delay because of a “simple” function that removes the unwanted strings.
So here is the messy SQL -
sql = "SELECT dbo.SHLSearchAllSource.CardCode, dbo.SHLSearchAllSource.BPName, dbo.SHLSearchAllSource.Block, dbo.SHLSearchAllSource.Street, dbo.SHLSearchAllSource.City" _
+ ", dbo.SHLSearchAllSource.County, dbo.SHLSearchAllSource.ZipCode, dbo.SHLSearchAllSource.Phone1, dbo.SHLSearchAllSource.Phone2" _
+ ", dbo.SHLSearchAllSource.Cellular, dbo.SHLSearchAllSource.E_Mail, dbo.SHLSearchAllSource.Type" _
+ ", (SELECT Count(*) FROM dbo.SHLSearchAllSource WHERE ((dbo.SHLSearchAllSource.BPName LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.Block LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.Street LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.City LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.County LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.ZipCode) LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone1) LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone2) LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Cellular) LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.E_Mail LIKE '%" _
+ strSourceText + "%'))) As Record_Count " _
+ "FROM dbo.SHLSearchAllSource WHERE ((dbo.SHLSearchAllSource.BPName LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.Block LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.Street LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.City LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.County LIKE '%" _
+strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.ZipCode) LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone1) LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone2) LIKE '%" _
+ strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Cellular) LIKE '%" _
+ strSourceText + "%') OR (dbo.SHLSearchAllSource.E_Mail LIKE '%" _
+ strSourceText + "%'))"
And here is the function -
[code]CREATE FUNCTION dbo.fnStripNonAlpha (@temp nvarchar(1000))
RETURNS nvarchar(1000)
AS
BEGIN
Declare @KeepValues as varchar(50)
Set @KeepValues = ‘%[^a-z0-9]%’
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, ‘’)
Return @Temp
END
GO[/code]
I have dropped the function from the address fields already and removed the name fields during further testing but would like to put them all back in place to make sure nothing slips by.
I was hoping to find an “elegant” solution rather than limiting the effectiveness of the search.
As an aside I don’t expect this search to be used very often as the users can use CLI from our soft phones to search on the incoming telephone number (about 70% + calls give us their CLI as mostly B2C) and/or a lot of customers have their reference numbers to hand when they call us.
Wow, brain fart over 
Cheers,
Paul
