convert string into int SQL Server

Question

This is the scenario:

My app will have the following:

A listbox (The checkbox property enabled) that will display a list of Something.
The user will select from the listbox (multiselect) by using the checkbox.
I will loop into All the checked items and store the ID's into an array. I will store the ID's into something like this separating the ID with a comma (1,2,3,4) and then I will use length -1 to delete the last comma.

How can I convert the string 1,2,3,4 into an integer type of data if my stored procedure is like this?

Select * from tblSomething Where ID in (1,2,3,4)

It would be better to use the appropriate data type throughout. SQL Server offers one type for storing multiple values - the table. You can use Table-Valued Parameters to pass tabular data from client applications to SQL Server, and of course, it can easily return tabular results. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 3, 2013 at 8:36

praveen · Accepted Answer · 2013-05-03 08:52:03Z

1

You can use the following SQL function.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CommaSeparatedToString]
(
   @psCSString VARCHAR(8000)
)

RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @sTemp VARCHAR(50)
WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                           LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END
RETURN
END

And call in your stored procedure like

Select * from tblSomething 
Where ID in (SELECT * FROM CommaSeparatedToString('1,2,3,4'))

edited May 3, 2013 at 8:52

praveen

12.3k1 gold badge44 silver badges50 bronze badges

answered May 3, 2013 at 8:31

Mukesh

8073 gold badges10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2059064 Over a year ago

Wow! Perfect, Simple and easily reusable. Thank you very much @Mukesh

snehal · Accepted Answer · 2013-05-03 08:31:39Z

0

You can use the

SELECT CAST(MyVarcharCol AS INT) FROM Table

SELECT CONVERT(INT, MyVarcharCol) FROM Table

refer this link http://msdn.microsoft.com/en-us/library/ms187928.aspx

answered May 3, 2013 at 8:31

snehal

1,8565 gold badges18 silver badges25 bronze badges

Comments

Sandeep Kumar · Accepted Answer · 2013-05-03 08:32:58Z

0

You need to create dynamic query for this

e.g you are getting list of values in @values paramter so prepare and run the dynamic query like this

    DECLARE @query NVARCHAR(500)
    DECLARE @values VARCHAR(200)
    SET @values='1,2'

    SET @query =N'Select * from tblSomething  Where ID in ( ' + @values + ')'
    SELECT @query
     EXEC @Query

answered May 3, 2013 at 8:32

Sandeep Kumar

8031 gold badge6 silver badges13 bronze badges

Comments

Deepak.Aggrawal · Accepted Answer · 2013-05-03 08:35:02Z

Use this function to split the value:

CREATE FUNCTION [dbo].[udfSplitCSV]
(
 @String varchar (max),
 @Delimiter varchar (10) = ','
)

RETURNS @ValueTable TABLE ([Row] int IDENTITY(1,1), [Value] varchar(max), [Length] int, [Duplicate] int NULL)

BEGIN

DECLARE @NextString varchar(max)
DECLARE @Pos int
DECLARE @NextPos int

IF  @String IS NULL RETURN 

 --Initialize
SET @NextString = ''
SET @String = @String + @Delimiter

 --Get position of first Comma
SET @Pos = charindex(@Delimiter,@String)
SET @NextPos = 1

 --Loop while there is still a comma in the String
 WHILE (@Pos <>  0)  
   BEGIN
   SET @NextString = RTrim(LTrim(SubString(@String,1,@Pos - 1)))
   INSERT INTO @ValueTable ([Value], [Length]) VALUES (@NextString, Len(@NextString))
   SET @String = SubString(@String,@Pos+1,Len(@String))
   SET @NextPos = @Pos
   SET @Pos  = CharIndex(@Delimiter,@String)
   END

UPDATE @ValueTable
   SET [Duplicate] = X.Duplicate
   FROM @ValueTable VT
   INNER JOIN (Select [Row], [Value], Row_Number() OVER (Partition By [Value] ORDER BY [Value], [Row]) as Duplicate FROM @ValueTable) X
      ON X.[Row] = VT.[Row]

RETURN
END

-- Select * from dbo.udfSplitCSV('a   , c b,c, a', ',')

Aijaz Chauhan · Accepted Answer · 2013-05-03 08:35:51Z

0

When you are storing a bunch of IDs into the array, store with single quote. so it will be ('1','2','3').

Then you no need to covert IDs into integer.

answered May 3, 2013 at 8:35

Aijaz Chauhan

1,6693 gold badges28 silver badges55 bronze badges

Collectives™ on Stack Overflow

convert string into int SQL Server

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related