How to test a string for numeric?

microsoft.public.sqlserver.programming

18 Aug 2005 9:21 PM

Snake

How do I test if a varchar column contains a numeric value?

18 Aug 2005 9:25 PM

Alejandro Mesa

What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390

AMB

Show quote

"Snake" wrote:

> How do I test if a varchar column contains a numeric value?
>

18 Aug 2005 10:44 PM

Aaron Bertrand [SQL Server MVP]

CREATE TABLE #foo
(
bar VARCHAR(12)
)

SET NOCOUNT ON

INSERT #foo SELECT 'aaaaa5aaaa'
INSERT #foo SELECT '5aaaa'
INSERT #foo SELECT 'aaaa5'
INSERT #foo SELECT 'xxxxx'
INSERT #foo SELECT '.'

SELECT bar, ContainsNumeric = CASE
WHEN bar LIKE '%[0-9]%' THEN 'Yes'
ELSE 'No'
END
FROM #foo

DROP TABLE #foo

Show quote

"Snake" <Sn***@discussions.microsoft.com> wrote in message
news:EF4FDF27-7F2B-4BF9-A8CE-6EE1E5C9C74F@microsoft.com...
> How do I test if a varchar column contains a numeric value?
>