MSSQL aggregate ignores where condition

Question

I have a strange problem: when I perform an aggregate function in a varchar column of type cast, I get "Msg 8114, level 16, state 5, line 1. Error converting the nvarchar to bigint data type". Queries where the sentence should filter out non-numeric values.

The table structure is similar to this:

IF EXISTS (SELECT * FROM sys.all_objects ao WHERE ao.name = 'Identifier' AND ao.type = 'U') BEGIN DROP TABLE Identifier END
IF EXISTS (SELECT * FROM sys.all_objects ao WHERE ao.name = 'IdentifierType' AND ao.type = 'U') BEGIN DROP TABLE IdentifierType END

CREATE TABLE IdentifierType
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Style] [int] NULL,
  CONSTRAINT [PK_IdentifierType_ID] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

CREATE TABLE Identifier
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [IdentifierTypeID] [int] NOT NULL,
  [Value] [nvarchar](4000) NOT NULL,
  CONSTRAINT [PK_Identifier_ID] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

ALTER TABLE Identifier WITH CHECK ADD  CONSTRAINT [FK_Identifier_IdentifierTypeID] FOREIGN KEY([IdentifierTypeID]) REFERENCES IdentifierType ([ID])
GO

Identifier.Value is a VARCHAR column; it may or may not contain numeric data. Filtering the request in IdentifierType.Style = 0 should mean that 'Value' returns only string representations of integers. The following is a request with the error "Msg 8114, Level 16, State 5, Line 1. Error converting the nvarchar to bigint data type."

SELECT
  MAX(CAST(Value AS BIGINT))
FROM 
  Identifier i,
  IdentifierType it
WHERE
  i.IdentifierTypeID = it.ID AND
  it.Style = 0

WHERE, 'AND ISNUMERIC (i.Value) = 1', . , . , :

SELECT
  *
FROM 
  Identifier i,
  IdentifierType it
WHERE
  i.IdentifierTypeID = it.ID AND
  it.Style = 0 AND 
  ISNUMERIC(i.Value) <> 1

(), . . , , ( 6 )

, MSSQL CAST , WHERE?

- - ?

, temp, MAX .

SELECT
  Value
INTO
  IdentifierClone
FROM 
  Identifier i,
  IdentifierType it
WHERE
  i.IdentifierTypeID = it.ID AND
  it.Style = 0

SELECT MAX(CAST(Value as BIGINT)) FROM IdentifierClone

.

.

Answer 1

REGEX, . , ISNUMERIC ,

CREATE TABLE tst (value nvarchar(4000))
INSERT INTO tst select '£'
-- Record found ...
SELECT * FROM tst WHERE value NOT LIKE '%[0-9]%'
-- No record found ...
SELECT * from tst where isnumeric(value) <> 1