CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Mihai Sirbu
Mihai Sirbu

Reputation: 25

Using CAST() and resulting an error

I'm trying to do a SELECT for concatenating 2 columns in MSSQL 2008R2.

COALESCE(SeriePolita, '') + COALESCE(CAST(NumarPolita AS numeric), '') as NrSeriePolita

Both SeriePolita and NumarPolita are NVARCHAR. The difference between them is that SeriePolita contains text and NumarPolita only number. I need to convert NumarPolita from 0008838630 to 8838630. As you ca see in the code line, i used CAST() for NumarPolita but i get this message:

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

Is there another way to covert a NVARCHAR to INT/NUMERIC? Thank you!

Upvotes: 1

Views: 93

Answers (2)

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

You need to cast it back to NVARCHAR to concatenate it, since concatenation of nvarchar with numeric is not possible:

COALESCE(SeriePolita, '') + COALESCE(CAST(CAST(NumarPolita AS numeric) AS NVARCHAR), '') as NrSeriePolita

Upvotes: 1

Glorfindel
Glorfindel

Reputation: 22651

You can use a CASE expression and ISNUMERIC:

SELECT

  CASE
    WHEN ISNUMERIC(NumarPolita)
      THEN COALESCE(SeriePolita, '') + COALESCE(CAST(NumarPolita AS numeric), '')
    ELSE ''
  END AS NrSeriePolita

  FROM ...

Alternatively, if you only need the records where this conversion is possible, you can use ISNUMERIC in the WHERE clause.

Upvotes: 0

Related Questions