Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Martin Smith Mar 27 '12 at 17:27

2 Answers 2

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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