Wednesday, March 7, 2012

Query Column with letters

I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?
Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?|||if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?

Well the data is already in there and we would rather have it disabled with the option of cleaning it up later rather than simply deleting such data.|||ok. Use the ISNUMERIC function... it'll return a 1 if the field is a valid number... 0 if it's not. That should be the easiest way to determine if there are letters in the postal code.|||Good idea, but I must allow for hyphens.|||Ya mean like

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT '12345' UNION ALL
SELECT 'Brett' UNION ALL
SELECT 'North Dallas 40' UNION ALL
SELECT '40-20' UNION ALL
SELECT '123.45' UNION ALL
SELECT '123456789012345678901234567890'
GO

SELECT * FROM myTable99
WHERE ISNUMERIC(REPLACE(Col1,'-','')) = 1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

No comments:

Post a Comment