Convert Nverchar Value Contain Comma To Int In SQL

I have Nvarchar Value As '7055,7085,7090,7035,7040,7050,7053,7055'

I Want To Convert It To Int.

Using Cast Or Convert It throw Error As Conversion failed when converting the nvarchar value '7010,7035,7045,7050,7053,7055,7085,7090,7040' to data type int.

Help Me To Convert It To Int

Thank You.
LVL 8
Naitik GamitSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What do you pretend to do? Split each number in the varchar field and convert it one by one to int?
0
Naitik GamitSoftware DeveloperAuthor Commented:
I want to use this converted int value to IN query as

Select A,B,C Fom Etc_Tbl Where ID IN ( )

When I am using nvarchar value it throw error as :
Conversion failed when converting the nvarchar value '7010,7035,7045,7050,7053,7055,7085,7090,7040' to data type int.

I am also try cast and convert but no change......
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Etc_Tbl is INT data type?
And what is the column name for you varchar values?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Naitik GamitSoftware DeveloperAuthor Commented:
yes............

Select A,B,C Fom Etc_Tbl Where ID IN ( )  

In my query ID is int and nvarchar field is named Role_CSV

so,i am trying as

Select A,B,C Fom Etc_Tbl Where ID IN ( Role_CSV )
Select A,B,C Fom Etc_Tbl Where ID IN (CAST(Role_CSV  AS INT ) )
Select A,B,C Fom Etc_Tbl Where ID IN (CONVERT(INT,Role_CSV ) )  
But it not convert Role_CSV to INT.
0
Ryan ChongCommented:
you can try customize:
CREATE FUNCTION dbo.Split3 ( @strString varchar(4000))

RETURNS  @Result TABLE(Value BIGINT)
AS
BEGIN
 
      DECLARE @x XML 
      SELECT @x = CAST('<A>'+ REPLACE(@strString,',','</A><A>')+ '</A>' AS XML)
     
      INSERT INTO @Result            
      SELECT t.value('.', 'int') AS inVal
      FROM @x.nodes('/A') AS x(t)
 

    RETURN
END   

DECLARE @str VARCHAR(4000) = '7010,7035,7045,7050,7053,7055,7085,7090,7040'
Select A,B,C From Etc_Tbl Where ID IN (  select Value from dbo.Split3(@str)  )

Open in new window


ref:
Convert Comma Separated String to Table : 4 different approaches
http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to think in another solution as for example why not to convert the INT column to VARCHAR and compare it in the WHERE clause:
Select A,B,C 
From Etc_Tbl 
WHERE Role_CSV LIKE '%'+CAST(ID AS VARCHAR)+'%'

Open in new window

0
Thomas H.Commented:
select a, b, c from etc_tbl
where charindex(cast(id as varchar(20)),'7040,7055,7090,7035,7040,7050,7053,7055') > 0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.