18May/100
CSV list to Int SQL Function
How many times have you wanted to pass in a comma delimited string of numbers to a SQL statement for an IN clause in a query against a Int field.
1 2 3 4 | SELECT * FROM MyTable WHERE ID IN ('2,3,4,5,6') Msg 245, Level 16, State 1, Line 1 Conversion failed WHEN converting the VARCHAR VALUE '2,3,4,5,6' TO DATA TYPE SMALLINT. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE FUNCTION [dbo].[CsvToInt] ( @Array VARCHAR(1000)) RETURNS @IntTable TABLE (IntValue INT) AS BEGIN DECLARE @separator CHAR(1) SET @separator = ',' DECLARE @separator_position INT DECLARE @array_value VARCHAR(1000) SET @array = @array + ',' While patindex('%,%' , @array) <> 0 BEGIN SELECT @separator_position = patindex('%,%' , @array) SELECT @array_value = LEFT(@array, @separator_position - 1) INSERT @IntTable VALUES (CAST(@array_value AS INT)) SELECT @array = stuff(@array, 1, @separator_position, '') END RETURN END |
Now running the following SQL statement works..
1 | SELECT * FROM PayPalAPI_NVPData WHERE ID IN ( SELECT IntValue FROM dbo.CsvToInt('2,3,4,5,6') ) |
Sponsors
Categories
- CakePHP (2)
- Classic ASP (VB Script) (14)
- CSS (2)
- HTML5 (1)
- Javascript Libraries/Frameworks (2)
- SmarterTrack (1)
- Software (3)
- T-SQL (4)
- VB.Net (1)
- WordPress (3)
Site Links
Blogroll
- Codango Web Development Reviews, Resources, and Inspiration.
- tuts+ From graphics to web development, audio to video and more, get the skills you want from our family of tutorial and resource sites.
- W3Avenue advice & resources for rapid web development
- WebAppers Hunting the Best Open Source Resources for Web Developers