Conversion failed when converting the nvarchar value to data type int

Question

I have a table of data with a primary key which generally takes the format of $$$$#####$$, although there a couple of exceptions to this where there is no number. I want to extract the number part of the key and then use it so I can generate unique primary keys.

I therefore created a view which contained a column showing only the numeric value and ignored any items which could not convert to numbers.

When I wrote a query to select a specific item from the view I get

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

and it would appear that although I specifically ignored the exceptions in the view they are still being references some how.

Any help would greatly be appreciated.

Matt

SQL Server will inline the view into the query and can rearrange the components of the query so that the cast to int happens before the filter. The only way around this is to use a `CASE` statement (or `TRY_CONVERT` if you are on SQL Server 2012). — Martin Smith, Aug 16 '12 at 11:09
Explained more fully here. [link](http://stackoverflow.com/questions/5191701/tsql-divide-by-zero-encountered-despite-no-columns-containing-0) — mattbloke, Aug 16 '12 at 13:36

score 0 · Answer 1 · edited May 23 '17 at 11:48

I want to extract the number part of the key and then use it so I can generate unique primary keys.

Use this Fastest way to remove non-numeric characters from a VARCHAR in SQL Server :

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Pass your column value to the function and then cast it to the integer. You can create another function that convert this checking that whether column value contain any numeric value after applying the above function.

Another Reference.

Hope this help

Conversion failed when converting the nvarchar value to data type int

1 Answers1