SELECT Items Based On String Manipulation

I am having some trouble creating a query that will preform some string manipulations on a field, and include this as part of the WHERE clause

For example I want to do something like this:


Code:

SELECTTable1.Column1,
Table1.Column2,
Table1.Column3,
Table2.Column1

FROMTable1
INNER JOIN Table2 ON Table1.UID = Table2.UID

WHERE(SET @Temp = Table2.Column1

--remove all 0's
SET @k = patindex('%[^0 ]%', @Temp)
WHILE @k> 0
BEGIN
SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
SET @k= patindex('%[^0 ]%', @Temp)
END
SELECT @Temp
) = ''



But of course this isn't working so much. I am wondering if I have to use a cursor?