Cast NTEXT to INT for calculation and comparison

Question

I need to convert an NTEXT column to an INT so I can perform some checks on it.

The first check would be is value = 0, and the second would be if it was an even or an odd number. I've got this mostly figured out except the conversion of the NTEXT to INT. So far what I have is this:

cast((cast(a.[Value1] as nvarchar(max))) as int) = 0

The column data looks like this '0'. When I run my query I get:

Conversion failed when converting the nvarchar value ''0'' to data type int.

What are my options to get around this; is there a way to make it ignore the quotation marks?

Answer 1

See comment by Martin Smith and http://stackoverflow.com/q/2133946/327165 for overview of why not to use ntext.

In order to get numeric records only, including records with numbers that have quotation marks try this: SELECT REPLACE(CAST(TextValue AS NVARCHAR(MAX)),'''','') FROM Table a WHERE ISNUMERIC(CAST(a.[Value1] AS NVARCHAR(MAX))) = 1

Answer 2

This may be overkill for your need, but you may want a parse-to-integer function which gracefully handles different formats, text values, etc.

CREATE TABLE #Test (A NTEXT)
INSERT INTO #Test (A) VALUES ('123'), ('ABC'), ('456')
SELECT A, dbo.ParseInteger(A) AS Parsed FROM #Test

Output:

A   Parsed
123 123
ABC NULL
456 456

Here's the UDF:

CREATE FUNCTION dbo.ParseInteger(@Input VARCHAR(30)) RETURNS BIGINT
AS BEGIN
    IF ISNUMERIC(@Input) = 0 RETURN NULL
    IF LTRIM(RTRIM(@Input)) IN ('.', '+', '-', ',') RETURN NULL
    SET @Input = REPLACE(@Input, ',', '')
    RETURN ROUND(CAST(@Input AS REAL), 0)
END

The NTEXT column is automatically cast to VARCHAR(30); just about any text value should be fine. It's not perfectly robust; an input of thirty spaces followed by "123" will not parse, for example.