The phone numbers (north american) could be written in the table in several
formats ex. (514) 645-8865 or 1 (450) 658-1788 or 14506532345 etc.

I think the best way to do a lookup is to look for a number that is made up
only of digits, all other characters having been removed, however it is
likely that the users would have written non-numeric characters in the
phonenumber field and may have written additiona info like extension
numbers. (I did not create the table definition , if I had I would have
prevented this from being done and created atomic fields, but I'm stuck with
this defintion a phone number field of varchar 50, ridiculous, anyways)

Is there a way to write an sql statement that would find a record containing
a phone like shown or any other possible format, if what we used the select
lookup value containg at most 11 numbers?

Any help would be appreciated

Bob

If all of your phone numbers are in north american format, then you could
replace all non-numerics, remove any leading 1s, and take the left 10
characters.  Then the user could enter a phone number, or a phone number and
area code.  In the former case you look at the last 7 characters, in the
latter you look at all 10.

USE tempdb;
GO

SET NOCOUNT ON;
GO

CREATE TABLE tmp
(
i INT IDENTITY(1,1),
pn VARCHAR(32)
);
GO

INSERT tmp(pn) SELECT '(514) 645-8865';
INSERT tmp(pn) SELECT '(450) 658-1788';
INSERT tmp(pn) SELECT '14506532345';
INSERT tmp(pn) SELECT '1 (450) 658-1788 x45';
GO

CREATE VIEW dbo.tmpView
AS
SELECT
  i,pn = SUBSTRING(pn, CASE WHEN LEFT(pn,1) = '1' THEN 2 ELSE 1 END, 10)
FROM
(
    -- you may need to add more characters here to replace
    -- or prevent them from getting in in the first place.
  SELECT i,pn = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(pn, '(', ''),
')', ''), ' ', ''), '-', '')))
   FROM tmp
) x
GO

SELECT i,pn
FROM dbo.tmpView
WHERE pn = '5146458865'
OR pn LIKE '%6581788';

DROP VIEW dbo.tmpView;
DROP TABLE tmp;



Of course what I will suggest is that, again assuming all north american
numbers, that you fix the data *before* you put it in the database.  Most
forms on the web enforce this on the user, e.g. they will say enter credit
card, numbers only, no dashes, no spaces.  If there are north american
numbers only, your form could easily have three entries, (___) ___-____ ...
this would prevent the optional leading 1 and will allow you to store the
three numeric-only components however you want, concatenated or separate.





Show quoteHide quote

Robert Dufour a écrit :
Show quoteHide quoteHi,

the best way I find over a 10 million number is to clean and reverse the
number, then index it.

CREATE TABLE T_TEL
(TEL_ID       INT NOT NULL PRIMARY KEY
  TEL_NUM      VARCHAR(20),
  TEL_REVERSE  CHAR(10))

Then to clean the number I use this UDF :

/****************************************************************************/
-- purge de caractères indésirables
/****************************************************************************/
-- exemple : F_RESTRICT('à Paris...?', 'abcdefghijklmnopqrstuvwxyz') =>
'aris'
CREATE FUNCTION F_RESTRICT (@IN VARCHAR (8000),
                             @CHARSOK VARCHAR(256))
RETURNS VARCHAR (8000)
AS
BEGIN
-- effets de bord
    IF @IN IS NULL OR @CHARSOK IS NULL RETURN NULL
    IF LEN(@IN) = 0 RETURN @IN
-- initialisation
    DECLARE @I INTEGER
    DECLARE @OUT VARCHAR(8000)
    SET @OUT = ''
-- lecture caractère par caractère
    SET @I =1
    WHILE @I <= LEN(@IN)
    BEGIN
       IF PATINDEX('%' + SUBSTRING(@IN, @I, 1)+ '%', @CHARSOK) > 0
          SET @OUT = @OUT + SUBSTRING(@IN, @I, 1)
       SET @I = @I + 1
    END
    RETURN @OUT
END
GO


To reverse and set it in the column I use à trigger :


CREATE TRIGGER E_IU_TEL ON T_TEL FOR UPDATE, INSERT
AS

UPDATE T_TEL
SET    TEL_REVERSE =
        SUBSTRING(
           COALESCE(
              REVERSE(
                 dbo.F_RESTRICT(T.TEL_NUM, '0123456789')
                     ), ''
                   ), 1, 10
                 )
FROM   T_TEL T
        INNER JOIN inserted i
              ON T.TEL_ID = i.TEL_ID
WHERE  T.TEL_NUM IS NOT NULL

Why reversing the number ???
Because significant searches are the last number, not the first due to
land prefix wich can be ommited if you are inside the country !

A +

Thank you both
Bob
Show quoteHide quote

Activity Monitor
Left Joins (*=)
IN operator versus BETWEEN and >= AND <=
Pass two parameters in query
Problem with SET QUOTED_IDENTIFIER ON