2

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?

1
  • 4
    Why are you storing integers as ntext? Wholly inappropriate datatype. ntext is (a) deprecated (b) Was intended for large strings > 8KB (c) is double byte as it is for storing strings containing more exotic characters than just numbers. Mar 27, 2012 at 17:27

2 Answers 2

1

See comment by Martin Smith and https://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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.