How to convert Varchar Number to intger in Sql

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

SQL

SQL-Server

, +

Hi All
I have to send the bank Id with IN SQL Quarry.
I write the below Quarry

SQL

declare @Bank_id varchar(4000)
set @Bank_id = '5,28'
SELECT CONVERT(int, CONVERT(int, @Bank_id));
select Bank_Id,Bank_Name from  Tbl_bank where bank_Id in (@Bank_id)

but i get the Error
"Conversion failed when converting the varchar value '5,28' to data type int."

Please give me quarry for the sending ID with In Quarry in to Stored procedure.
Thanks In Advanced

Regards,
Ravi Sharma

Posted 14-Apr-12 2:41am

Ravi Sharma 2

Updated 14-Apr-12 17:50pm

v3

Add a Solution

4 solutions

Solution 1

Have You tried set @Bank_id = '5.28'?

Posted 14-Apr-12 4:04am

Jorge J. Martins

Comments

Ravi Sharma 2 14-Apr-12 23:49pm

Yes , I want to Send ID to the Stored procedure through In Quarry.

Solution 2

If I look in your question,
it seems you need to fetch the data of banks whose id's are
5 and 28 these two banks's data you need to fetch.
but you got confused, you are trying '5,28' is you bank id for one bank.

don't convert just try

declare @Bank_id varchar(4000)
set @Bank_id = '5,28'
select Bank_Id,Bank_Name from Tbl_bank where bank_Id in (@Bank_id)

Posted 14-Apr-12 4:35am

kishore sharma

Comments

Ravi Sharma 2 14-Apr-12 23:47pm

Yes i try for the same but got the Same Error
Conversion failed when converting the varchar value '5,28' to data type int.
Please provide me proper Quarry to send the ID in Stored Procedure through SQL IN Quarry.

Solution 3

Your solution is here: http://codehighstreet.com/Snippets/parsing_comma_separated_values_into_table_in_sql_server.aspx[^]

Posted 15-Apr-12 1:05am

Jorge J. Martins

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kishore sharma · Accepted Answer · 2012-04-15T18:14:00

Hello,
Use The Following Function To split such values
Execute this and u need to use
Example:

SQL

declare @Bank_id varchar(4000)
set @Bank_id = '5,28'
SELECT CONVERT(int, CONVERT(int, @Bank_id));
select Bank_Id,Bank_Name from  Tbl_bank where bank_Id in (select data from split(@Bank_id,','))

where split takes two arguements
1st:the value to be split(@Bank_id)
2nd:The symbol used to split(,)

you will get wht you want

SQL

Create FUNCTION [dbo].[Split]
(
	@RowData nvarchar(4000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(400)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
end

Thanks & Regards