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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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?
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......
Vitor MontalvãoMSSQL Senior EngineerCommented:
Etc_Tbl is INT data type?
And what is the column name for you varchar values?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

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

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
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.