sj999 (10/16/2012)
I have a table TXNSColumn A is int (3)
Column B is varchar 10
I wish to remove all non-alphanumeric characters from Column B
using a tally table.
Lets set a limit of 20 rows to the tally table.
Any ideas?
You only need 10 rows, because your B column is VARCHAR(10).
Here's the sample data I used: -
SELECT X AS A, N AS B
INTO TXNS
FROM (VALUES(1,'r^$%^edsad'),(2,'fwsa38#'))a(X,N);
And here's the solution: -
WITH Tally(N) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))a(N))
SELECT a.A, a.B, (SELECT stripped
FROM TXNS b
CROSS APPLY (SELECT CASE WHEN PATINDEX('[A-Za-z0-9]',SUBSTRING(B,N,1)) = 1
THEN SUBSTRING(B,N,1)
ELSE '' END
FROM Tally
WHERE N <= LEN(B)) c(stripped)
WHERE a.A = b.A
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(10)') AS stripped
FROM TXNS a;
Note that I've used an inline CTE tally table rather than a physical one. This can be replaced if required.