-1

I have the following problem:

I have a table ArticleDescription with columns like this:

ID (int)            Description (varchar(max))
1                   Muller Bogen, chrome-14440416700-55
2                   Muller Bogen, chrome-14440416700-55
3                   Muller Geruchverschluss 1 1/4¨, black-11188870-00
                    Muller Ma.02 Waschtisch-Wand-Einhandbatterie ohne Ablaufgarnitur, white-12345678-08

I only want to convert the datatype of the column Description to int.

I have tried:

SELECT (case when isnumeric(Description) = 1 then cast(Description as int) end)
FROM   ArticleDescription

But I get the following message:

Meldung 245, Ebene 16, Status 1, Zeile 20 Fehler beim Konvertieren des varchar-Werts "Dornbracht Bogen-04240418900-00" in den int-Datentyp.

Luebeck
  • 9
  • 1
    Rows don't have data types. It is a bit unclear what your data looks like and what you really want to do. Can you put data in a *tabular* format and provide an appropriate database tag? – Gordon Linoff Jun 29 '20 at 12:49
  • Aside: [`IsNumeric()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql) is notoriously [problematic](http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/). [`Try_Parse`](https://learn.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-ver15) is more appropriate. – HABO Jun 29 '20 at 13:58
  • 1
    What integer do you expect from this `Muller Bogen, chrome-14440416700-55`? – Scratte Jun 29 '20 at 15:33

1 Answers1

0

Data columns must all be of the same type. In order to do this, you will need to have an acceptable alternative value for the remainder of the rows.

declare @foo table(
    ID int,
    Description varchar(20) null
)

insert into @foo
values(1,'one')
,(2,'2')
,(3,'')
,(4,null)

select 
ID,
CASE WHEN ISNUMERIC(Description) = 1 then CAST(Description as INT) ELSE null END as [Parsed Description]
from @foo

or you can add a where clause to only select numeric descriptions

select 
ID,
CAST(Description as INT)  as parsed_pescription
from @foo foo
where ISNUMERIC(Description) = 1
Brian Edwards
  • 135
  • 8
  • The proposed solution results NULL. I have a column "Description" in varchar(max) as datatype. There data that looks like this: Muller Ma.02 Waschtisch-Wand-Einhandbatterie ohne Ablaufgarnitur, white-12345678-08. How I can convert this data in integer? – Luebeck Jun 29 '20 at 14:17
  • The biggest obstacle to converting this to an int is finding a pattern so that you can isolate the part that is meant to be converted. Take a look at this answer to see if it helps https://stackoverflow.com/questions/10443462/how-to-get-the-numeric-part-from-a-string-using-t-sql – Brian Edwards Jul 01 '20 at 20:24