How to convert nvarchar with comman and double Apostrophe(")

Question

i have table with column comment as a nvarchar,when i am trying to convert nvarchar into int,i am getting error like Conversion failed when converting the varchar value '"2, 9, 10, 25"' to data type int can you guys help me please

What integer value are you expecting for `'"2, 9, 10, 25"'`? — Shannon Severance, Jul 12 '17 at 16:42
i have single row like "2,9,10,25" but i want 2 ,9,10,25 as a integer. i am trying to convert this varchar to int and using loop after that — Rab, Jul 12 '17 at 16:43
Comment is a column name and my first row value is "2,9,10,25" — Rab, Jul 12 '17 at 16:46
_2 ,9,10,25_ is not **a** integer. It is four integeries. Do you want that string converted to a table, with four rows, one for each of 2, 9, etc? — Shannon Severance, Jul 12 '17 at 16:51
Storing multiple values in a single value like that is not a good approach. But you will need a string splitter. Here are some good options. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Jul 12 '17 at 17:04
Possible duplicate of [T-SQL How to convert comma separated string of numbers to integer](https://stackoverflow.com/questions/18744391/t-sql-how-to-convert-comma-separated-string-of-numbers-to-integer) — Tab Alleman, Jul 12 '17 at 17:40

score 1 · Answer 1 · answered Jul 12 '17 at 17:50

You can use String_Split if you are using SQL Server 2016 else you need to do some xml processing to split as below:

;With cte as (
    Select xm = CAST('<x>' + REPLACE((SELECT REPLACE(REPLACE(col1,'"',''),', ','$$$SSText$$$') AS [*] FOR XML PATH('')),'$$$SSText$$$','</x><x>')+ '</x>' AS XML) 
        from #test  
) Select [Value] from cte c
cross apply ( Select y.value(N'text()[1]', N'int') as value 
FROM c.xm.nodes(N'x') as x(y) ) a

Your Input table

create table #test (col1 nvarchar(20))
insert into #test (col1) values ('"2, 9, 10, 25"' )

How to convert nvarchar with comman and double Apostrophe(")

1 Answers1