question

liton avatar image
liton asked

Count text and numeric records

I have field name Account, which is a varchar field. It holds the account number, which is numeric and can have dash (-). I see several records in the Account field has street address instead of account number. Is there a way to do a count to see how many records have the street address in the account field? I mean is there a query where I can do a search for the records there have both text and numeric value?
count
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
You could try something like SELECT COUNT(*) FROM MyTable WHERE Account LIKE '%[a-z]%' That'll give you a count of all records which contain an alphabetic character (assuming non-case-sensitive). If you have a case-sensitive environment, then try: SELECT COUNT(*) FROM MyTable WHERE Account LIKE '%[a-zA-Z]%' Of course, depending on your collation, that might not handle accents properly... References: * [T-SQL `LIKE`][1] keyword - check the discussion on Wildcard characters in the section about the _pattern_ argument * [SQL Server Collations][2] - gives information about how SQL Server can be configured to handle case-sensitive or accent-sensitive data. [1]: http://msdn.microsoft.com/en-us/library/ms179859.aspx [2]: http://msdn.microsoft.com/en-us/library/ms143726.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.