I quite often find random phone numbers in my phone log and so want to search GoldMine to see who has called me. This is a harder than it seems though: a phone number, if it exists at all, may be in any one of 3 fields in contact1 (4 if you count the fax field), or in either the phone or fax fields in contsupp.

What about Universal Search, I hear you ask? Well, yes, that’s fine if I want to return results from emails, linked documents and goodness knows what else – which I don’t.

So I cooked up a function to do the job.

Run the script (below) in SQL Manager to create the function. Make sure that you set your database name in the USE statement.

Then, to search for a phone number from the GoldMine SQL Query window:

select * from fPhoneSearch(‘020 7549 1618’)

The string within the brackets is the number that you are looking for. Partial strings are equally valid:

select * from fPhoneSearch(‘0207’)

The script ignores any special characters in the phone fields and in the search term when doing the comparison.


USE goldmine
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fPhoneSearch]') and OBJECTPROPERTY(id, N'IsTableFunction') = 1)
drop function dbo.[fPhoneSearch]
GO

CREATE FUNCTION fPhoneSearch
(
@SearchStr nvarchar(100)
)
returns @results TABLE
(
accountno varchar(25),
company varchar(50),
contact varchar(50),
phone varchar(30)
)
AS
BEGIN
set @SearchStr=replace(replace(replace(replace(replace(@SearchStr,' ',''),'(',''),')',''),'-',''),'+','')
insert into @results
select
accountno, company, contact, phone1
from
CONTACT1
where
replace(replace(replace(replace(replace(phone1,' ',''),'(',''),')',''),'-',''),'+','')
like '%'+@SearchStr+'%'
UNION
select
accountno, company, contact, phone2
from
CONTACT1
where
replace(replace(replace(replace(replace(phone2,' ',''),'(',''),')',''),'-',''),'+','')
like '%'+@SearchStr+'%'
UNION
select
accountno, company, contact, phone3
from
CONTACT1
where
replace(replace(replace(replace(replace(phone3,' ',''),'(',''),')',''),'-',''),'+','')
like '%'+@SearchStr+'%'
UNION
select
accountno, company, contact, fax
from
CONTACT1
where
replace(replace(replace(replace(replace(fax,' ',''),'(',''),')',''),'-',''),'+','')
like '%'+@SearchStr+'%'
UNION
select
c1.accountno, c1.company, 'ac: '+cs.contact, cs.phone
from
CONTACT1 as c1, CONTSUPP as cs
where
c1.ACCOUNTNO=cs.ACCOUNTNO and
replace(replace(replace(replace(replace(cs.phone,' ',''),'(',''),')',''),'-',''),'+','')
like '%'+@SearchStr+'%'
UNION
select
c1.accountno, c1.company, 'ac: '+cs.contact, cs.fax
from
CONTACT1 as c1, CONTSUPP as cs
where
c1.ACCOUNTNO=cs.ACCOUNTNO and
replace(replace(replace(replace(replace(cs.fax,' ',''),'(',''),')',''),'-',''),'+','')
like '%'+@SearchStr+'%'

RETURN
END
GO