>I am not sure the fastest way to do this kind of search in sql server, maybe a regular expression?
>
>Let's say I have a 6 character varchar field. In that field are license plate nunbers, such as "L1 234", "QA3333", etc. Now the user enters via a telephone 6 numbers. (I cannot control the IVR portion and must deal with the 6 numbers.) Now given the phone pad, if a user enters "235466" that could really mean:
>235466
>A35466
>B35466
>C35466
>2A5466
>2B5466
>
>etc for millions of combinations. I need to pull the recordfs that match any of those possibilities.
>
>Comments?

If you think a phone dial pad, it is not really millions of combinations. For 6 figures it is less than 16000 and is a constant set that you can create as a 'tally' table.

UPDATE: Sorry I didn't have time when I first replied this. For the given number '235466' combinations are 4096. Here is the T-SQL code as a sample:

DECLARE @dialpad TABLE
(
    id CHAR(1),
    dial INT
);
INSERT @dialpad
(
    id,
    dial
)
VALUES
('0', 0),
('1', 1),
('2', 2),
('A', 2),
('B', 2),
('C', 2),
('3', 3),
('D', 3),
('E', 3),
('F', 3),
('4', 4),
('G', 4),
('H', 4),
('I', 4),
('5', 5),
('J', 5),
('K', 5),
('L', 5),
('6', 6),
('M', 6),
('N', 6),
('O', 6),
('7', 7),
('P', 7),
('Q', 7),
('R', 7),
('S', 7),
('8', 8),
('T', 8),
('U', 8),
('V', 8),
('9', 9),
('W', 9),
('X', 9),
('Y', 9),
('Z', 9);

DECLARE @numberdialed CHAR(6) = '235466';
DECLARE @digit1 INT = SUBSTRING(@numberdialed, 1, 1);
DECLARE @digit2 INT = SUBSTRING(@numberdialed, 2, 1);
DECLARE @digit3 INT = SUBSTRING(@numberdialed, 3, 1);
DECLARE @digit4 INT = SUBSTRING(@numberdialed, 4, 1);
DECLARE @digit5 INT = SUBSTRING(@numberdialed, 5, 1);
DECLARE @digit6 INT = SUBSTRING(@numberdialed, 6, 1);

SELECT d1.id + d2.id + d3.id + d4.id + d5.id + d6.id AS dialed
FROM
(SELECT id FROM @dialpad WHERE dial = @digit1) d1 ,
(SELECT id FROM @dialpad WHERE dial = @digit2) d2 ,
(SELECT id FROM @dialpad WHERE dial = @digit3) d3 ,
(SELECT id FROM @dialpad WHERE dial = @digit4) d4 ,
(SELECT id FROM @dialpad WHERE dial = @digit5) d5 ,
(SELECT id FROM @dialpad WHERE dial = @digit6) d6;