While it can be done it will never be able to use an index properly and will
always scan.  But you can use REPLACE() and specify the string to be
replaced as a parameter to the sp and replace it with an empty string.

You can drop characters from a string with nested REPLACE()
functions:

SET my_col  -- removes digits
  = REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(my_col, '0', ''),
     '1', ''),
    '2', ''),
   '3', ''),
  '4', ''),
 '5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '');

The same idea can be used for the alphabet.

If the case is with removing non-numeric characters, then you can use
something like the code below (requires SQL Server 2005). If the
non-matching portion can be a mix of numeric and non-numeric characters,
then it is a lot more difficult. In the code below you can replace the
reference to the system table master..spt_values with auxiliary table with
numbers.

CREATE FUNCTION dbo.CleanNonNumeric (@string NVARCHAR(100))
RETURNS NVARCHAR(100)
BEGIN
RETURN
 (SELECT CAST(
         (SELECT CASE
                  WHEN SUBSTRING(@string, n, 1) LIKE '[0-9]'
                  THEN SUBSTRING(@string, n, 1)
                  ELSE ''
                 END
         FROM (SELECT number
               FROM master..spt_values
               WHERE type = 'P'
                 AND number BETWEEN 1 AND 100) AS Nums(n)
         WHERE n <= LEN(@string)
         FOR XML PATH('')) AS NVARCHAR(100)));
END
GO

CREATE TABLE Foo1(
keycol NVARCHAR(100) PRIMARY KEY,
datacol CHAR(1));

CREATE TABLE Foo2(
keycol NVARCHAR(100) PRIMARY KEY,
datacol CHAR(1));

INSERT INTO Foo1 VALUES (N'12345345', 'a');
INSERT INTO Foo2 VALUES (N'1234XYZ5345', 'b');

SELECT A.keycol, B.keycol, A.datacol, B.datacol
FROM Foo1 AS A
JOIN Foo2 AS B
 ON A.keycol = dbo.CleanNonNumeric(B.keycol);

Plamen Ratchev
http://www.SQLStudio.com

CREATE TABLE tfoo1(itemnumber varchar(20));
CREATE TABLE tfoo2(itemnumber varchar(20));

INSERT INTO tfoo1 VALUES ('12345XYZ345');
INSERT INTO tfoo1 VALUES ('12XYZ345345');
INSERT INTO tfoo1 VALUES ('12345345XYZ');
INSERT INTO tfoo1 VALUES ('12345ABCD345');
INSERT INTO tfoo1 VALUES ('12ABCD345345');
INSERT INTO tfoo1 VALUES ('12345345ABCD');

INSERT INTO tfoo2 VALUES ('XYZ');
INSERT INTO tfoo2 VALUES ('ABCD');

SELECT *
    FROM tfoo1
    JOIN tfoo2
        ON CHARINDEX(tfoo2.itemnumber, tfoo1.itemnumber) > 0;

DROP TABLE tfoo1;
DROP TABLE tfoo2;

Vern Rabe

Mariano:

The CHARINDEX function could also be used: