SQL - Convert String of numbers to list of integers

Question

am passing in a string of numbers and would like to convert this string into a list of numbers e.g.

SELECT personID from person WHERE personID IN ('927,6944')

How do I convert ('927,6944') to (927,6944)

This is the error message I get when I try and run the query:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '927,6944' to data type int.

This answer may also be usable for your needs: http://stackoverflow.com/questions/18744391/t-sql-how-to-convert-comma-separated-string-of-numbers-to-integer — Pete, May 14 '14 at 16:21
I am passing '927,6944' as a string value that is generated via java code. This value will typically be different and can contain a larger String of numbers. Yes, this is a SQL message that I get when I run the query on SQL server. — scot0308, May 14 '14 at 16:24
If you are passing the string to a stored procedure parameter then take a look at this post: http://stackoverflow.com/questions/19334380/how-to-split-explode-comma-delimited-string-field-into-sql-query/19335966#19335966 — bsivel, May 14 '14 at 18:03
@podiluska, you have used your gold powers poorly. This is not about splitting a string. Please fully read and understand a question before exercising your new powers. You must be 8 times more certain that its a dupe. — crthompson, May 14 '14 at 18:19
I was able to use the post above that bsivel provided to create a temp table that converts the values passed into the stored procedure then join on the temp table. Thanks everyone for the help. — scot0308, May 14 '14 at 18:50
@podiluska And now the [real answer](http://stackoverflow.com/questions/19334380/how-to-split-explode-comma-delimited-string-field-into-sql-query/19335966#19335966) (that _would_ be a dupe of this) is marked as a dupe.. by you. Great way to prove your point i suppose. — crthompson, May 14 '14 at 19:56

score 1 · Answer 1 · answered May 14 '14 at 16:17

1

Remove the quotes (quotes imply varchar)

SELECT personID FROM person WHERE personID IN (927, 6944)

answered May 14 '14 at 16:17

JasonSec

614
5
12

Also, replaceing `, ` with `', '` would fix the quote issue to give you `IN ('972','6944')`. The conversion would happen properly. – crthompson May 14 '14 at 16:24
OP says that he is **passing the values from the program**. – Raging Bull May 14 '14 at 16:25
1

USING QUOTES ON INTEGERS!!! KILL IT WITH FIRE!!! – JasonSec May 14 '14 at 16:25
Wish it were that easy, but I'm passing this value in as a string. The amount of numbers in the string can vary. i.e. it won't always be '927, 6944'. – scot0308 May 14 '14 at 16:25
1

You're passing in the quotes too? Remove them or (god help us all) do what paqogomez suggested above. – JasonSec May 14 '14 at 16:27

Raging Bull · Answer 2 · 2014-05-14T16:33:40.477

1

You need to use dynamic sql. Pass the value '927,6944' as @Parameter

DECLARE @Parameter varchar(100)
DECLARE @Query varchar(200)
SET @Parameter='927,6944'   --An example

SET @Query='SELECT personID from person WHERE personID IN ('+@Parameter+')'
EXEC(@Query);

Explanation:

While executing, @Query contains the following:

SELECT personID FROM person WHERE personID IN (927, 6944)

OR

You need to make the whole query from the program itself and execute it directly.

edited May 14 '14 at 16:33

answered May 14 '14 at 16:18

Raging Bull

18,593
13
50
55

This is bad practice and potentially dangerous. – podiluska May 14 '14 at 18:47

score 0 · Answer 3 · answered May 14 '14 at 16:32

I suspect that it is possible to generate this query from your java code without the quote marks. The string with these numbers in it need not contain quote marks when being injected into the SQL. If for some reason the quote makes are not easy to remove when making the query, try passing it through a string replace method in your java code that will strip out the quote marks.

SQL - Convert String of numbers to list of integers

3 Answers3