SQL convert nvarchar into decimal dynamic rows

Question

I am not so familiar with using convert in SQL so thats why i am stuck with the following situation:

The column has a nvarchar(max) and i would like to convert it into decimal (18,2). But there are some rows that consist a "full" amount (see red box). For all values with the full amount i would like to have it as 1222,00

When creating a SQL view I got this error: Error converting data type nvarchar to numeric.

How can i still convert this column into decimal?

Many thanks

Here is few examples that where already answered: MySQL, SQL-Server — Aarlaneth
– Aarlaneth, Commented Oct 28, 2022 at 6:56
Hi @Aarlaneth unfortunatly, this suggestion doesnt work for me. — Regazzi
– Regazzi, Commented Oct 28, 2022 at 8:35
I got a error : Error converting data type nvarchar to numeric when creating the follow SQL View. I think because some of the records of the column have already a decimal values and two records have a full amount such as 1222 and 1247. Any suggestions @Aarlaneth CREATE VIEW viewB AS SELECT [Category] as Year, CONVERT(decimal(18,2),[Zorg en welzijn(smal)]) as [Zorg en welzijn(smal)] FROM dbo.[tableA] GO — Regazzi
– Regazzi, Commented Oct 28, 2022 at 8:38

Aarlaneth · Accepted Answer · 2022-10-28 08:55:45Z

1

From your pictures it seems to me like you are using SQL-Server. If so your problem is having , as decimal point instead of . It has nothing to do with having whole and decimal numbers in your data.

So you should replace it before converting data.

SELECT CONVERT(decimal(18,2), REPLACE(ColumnName,',','.')) FROM TableName

DB<>fiddle

edited Oct 28, 2022 at 8:55

answered Oct 28, 2022 at 8:50

Aarlaneth

5306 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL convert nvarchar into decimal dynamic rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related