Your best bet is to separate out the numeric part from the character part
into two separate columns. Here's a simple sample query that you could use
as a basis for that. It's not going to be extremely efficient - I'd
recommend splitting them up one time permanently if I were you:

CREATE TABLE #t (n VARCHAR(100))
INSERT INTO #t (n)
SELECT '1'
UNION SELECT '1A'
UNION SELECT '2'
UNION SELECT '34'
UNION SELECT '3B'
UNION SELECT '3BC'
UNION SELECT '4'
GO

WITH Numbers (num)
AS
(
SELECT 1
UNION ALL
SELECT num + 1
FROM Numbers
WHERE num < 100
)
SELECT CAST(SUBSTRING(#t.n, 1, MIN(num) - 1) AS INTEGER) AS NumPart,
SUBSTRING(#t.n, MIN(num), 255) AS AlphaPart
FROM Numbers
CROSS APPLY #t
WHERE SUBSTRING(#t.n + 'Z', num, 1) BETWEEN 'A' AND 'Z'
GROUP BY #t.n;


"Paul Shapiro" <paul (AT) hideme (DOT) broadwayData.com> wrote