How to convert string(comma seperated) to int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to convert string(comma seperated) to int

Author

Topic

maruthi_p
Starting Member

8 Posts

Posted - 2010-03-01 : 09:43:18

Hi,

I'm having a asp.net listbox on the front end webform with multiple selection, after user selecting multiple items, i'm capturing the DataKeyValue of listbox(which is amenity_id of bigint) and looping all the selected items to a string with comma sepearated values.

For example if user selects first 4 options, my output string will be like this (1,2,3,4) and i'm passing this as a string type to my data access layer and then to my below stored proc.

I'm geting this error while inserting.. i know that my data type is of bigint and i'm trying to insert string type..

can anyone please help me out.. i need to convert the string type to INT type and insert data. below are my stored procs:

ALTER PROCEDURE [usp_add_amenities] 
	(-- Add the parameters for the stored procedure here
	@amenities_id_list varchar(4000),
	@id varchar(50))
AS
BEGIN
SET NOCOUNT ON;
		DECLARE @pos int, @curruntLocation char(20)
		SELECT @pos=0
		--SELECT @input = '1234,2345,3456'
		SELECT @amenities_id_list = @amenities_id_list + ','
		WHILE CHARINDEX(',',@amenities_id_list) > 0
		BEGIN
		SELECT @pos=CHARINDEX(',',@amenities_id_list)
		SELECT @curruntLocation = RTRIM(SUBSTRING(@amenities_id_list,1,@pos-1))
		INSERT INTO ref_amenities (amenity_id, id) VALUES (@curruntLocation, @id)
		END	
END

This is where i'm splitting the comma seperated values and inserting them into table

can anyone please help me..

awaiting your response,

Many Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:53:25

INSERT INTO ref_amenities (amenity_id, id) VALUES (@curruntLocation, @id)

should be

INSERT INTO ref_amenities (amenity_id, id) VALUES (cast(@curruntLocation as bigint), @id)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:48:17

you could use the below function if you need

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-01 : 18:08:39

And for reference:
http://www.sommarskog.se/arrays-in-sql.html

maruthi_p
Starting Member

8 Posts

Posted - 2010-03-01 : 23:35:52

quote:
INSERT INTO ref_amenities (amenity_id, id) VALUES (@curruntLocation, @id)

should be

INSERT INTO ref_amenities (amenity_id, id) VALUES (cast(@curruntLocation as bigint), @id)

Hi,
I've tried with the above modification, But still it is throwing an error "conversion failed varchar to bigint"..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 02:28:31

Can you post the table structure?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 08:47:41

quote:
Originally posted by maruthi_p

quote:
INSERT INTO ref_amenities (amenity_id, id) VALUES (@curruntLocation, @id)

should be

INSERT INTO ref_amenities (amenity_id, id) VALUES (cast(@curruntLocation as bigint), @id)

Hi,
I've tried with the above modification, But still it is throwing an error "conversion failed varchar to bigint"..

where are you getting next value for @amenities_id_list? I think you should remove the extracted value from main string each time inside loop.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources