Convert a list of values from VARCHAR to INT
-
Hi,
I have a list of values 1,2,3,4,5 that I'm passing from the client to a stored procedure. I pass those values as a string such as '1,2,3,4,5' then I want to convert those values to INT because the field datatype is INT. I'm not using dynamic SQL. How would I handle something like this?
Thanks,
-
Function split - set based static sql :
--Start generating objects
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber < LEN(@vcDelimiter + @IDs + @vcDelimiter)
) dtSplitted where len(dtSplitted.EachID) > 0
GO
--Now you are ready to use the function in the query, here are 2 exemples :
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
-
Small version specific to Character to integer conversion
DECLARE @STR VARCHAR(100),
@VAL VARCHAR(10),
@L INT
SET @STR= '1,2,3,4,5,6,7,8,9,10,0'
DECLARE @tab TABLE (VAL INT)
WHILE DATALENGTH(@STR)>0
BEGIN
IF (DATALENGTH(@STR)=1 AND ISNUMERIC(@STR)=1)
BEGIN
INSERT INTO @tab VALUES (@STR)
BREAK
END
ELSE
BEGIN
SET @L=CHARINDEX(',',@STR)
SET @val=LEFT(@STR,@L-1)
SET @STR=RIGHT(@STR, LEN(@STR)-@L)
INSERT INTO @tab VALUES (@VAL)
END
END
SELECT * FROM @tab
-
Gopi,
Try passing this into your version....
SET @STR= '1,.,,$,2d01,6,7,8,9,10,0'
ISNUMERIC isn't valid as an IS ALL DIGITS function. You must use NOT LIKE '%[^0-9]%' instead...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Joe,
I tend to lean towards Remi's solution simply because it has no loop although I believe the loop would be just as fast in this case (probably only until the Tally table is in memory) and it allows for a bit more individual character checking than using a numbers table like Remi did.
However, a numbers table (I call it a "Tally" table, just sounds cool to me) has dozens of uses that will help you eliminate cursors and loops (dozens of numbers/tally table solutions on this forum, alone). In fact, since you're making a proc, you don't even need to create a function for this one (although a split function like what Remi made is very useful...). In fact, on projects that use a Tally table a lot, I'll just pin it into memory... nasty fast that way.
... and, I assume that if you want all integers, you don't want to process anything at all if an illegal character shows up in the parameter.
First, let's make a permanent Tally table with the equivelent of 30 years worth of numbers in it for doing complex data functions....
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
... don't worry... although it has lots of rows, it's actually a tiny table and, for all the utility it offers, it would be worth it at twice the size. It uses INT so you don't end up with implicit conversions slowing you down during certain conversions.
Now, you're problem becomes very easy... and, we can also tell which parameter's have what in them by number...
--===== This could either be a part of your proc or a function you call from
-- within the proc like Remi did. Most of the bulk of this code is comments.
--===== This variable represents the input parameter that you feed to a sproc
DECLARE @Parameter VARCHAR(100)
SET @Parameter = '1,2,3,4,5'
--===== Since this is very small, probably OK to use a table variable here...
-- Create a table to hold the results of the split.
DECLARE @Split TABLE (ParmNum INT IDENTITY(1,1) PRIMARY KEY, ParmVal INT)
--===== Let's check the whole parameter to make sure that we have only numeric digits
-- and commas in the parameter. If ok, populate the split table...
IF @Parameter NOT LIKE '%[^,0-9]%'
BEGIN
SET @Parameter = ','+@Parameter+',' --Just to save a bit of performance time
INSERT INTO @Split (ParmVal)
SELECT SUBSTRING(@Parameter,n+1,CHARINDEX(',', @Parameter, n+1)-n-1)
FROM dbo.TALLY
WHERE SUBSTRING(@Parameter, n, 1) = ','
AND n < LEN(@Parameter)
END
--===== Do a quick little demo of what's in the @Split table now...
SELECT * FROM @Split
--===== Do another quick little demo to show you can select any
-- paramter by number (6 won't have anything in it for this example,
-- but it doesn't blow up. Can be handy)
SELECT ParmVal FROM @Split WHERE ParmNum IN (2,4,6)
If you have any questions about this solution, post back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I usually don't include the pin table part in that function... It already gives enough head aches as is
.For those interested look up the dbcc pintable command. You simply have to put the code in a stored proc in the master database and make that proc execute on start up.
-
Not sure that's true... what you say will certainly guarantee that all the pages of the table are loaded into memory at startup... but even a simple SELECT * from the table will do that after the server has been started. Sure, the first time it's used, it won't be any faster but the first use pins the pages... after that, it's permanently pinned until you unpin it or bounce the server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry for the confusion. I was reffering to the select that was giving the headaches to the greener guys... so that's why I don't include the pin table in there
. -
Thanks for all the help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply