October 23, 2008 at 3:14 am
Hi,
i'm a beginner with sql server and t sql !
i have a store procedure who take some parameters!
one of these parameters is a nvarchar(4000)
when i execute my storep procedure and fill parameter i receive this error:
Conversion failed when converting the nvarchar value '1251,3352,4194,3367,4501,3372,4193,2109'
to data type int.
my question is how to convert a varchar to int !
i've try to do this but no succes
select ...
where condition in (CAST(int), @PostionsList)
Thanks for your idea
Christophe
October 23, 2008 at 3:48 am
The problem is that your are trying to convert a string that has a commas in it these are non-numeric characters and will not be able to covert into an int.
You need to split the string up into the individual values then convert these.
October 23, 2008 at 1:44 pm
Hello,
Here is a string parsing routine in case you've never done one in TSql.
declare @tStr varchar(4000),
@strLen int,
@flag int,
@target varchar(10),
@tIntint,
@pos int
set @tStr = '1234,5678,9012,3456,444444'
set @strLen = len(@tStr)
set @flag = 1
while ( @flag = 1 )
begin
-- get the position of the first comma
select @pos = patindex('%,%',@tStr)
if ( @pos > 0 )
begin
-- get the integer
set @target = ''
set @target = substring(@tStr,1,@pos - 1 )
set @tInt = convert(int,@target)
-- SHOW THE INT
select @tInt
-- shorten the initial long string
if ( len(@tStr) > 0 )
begin
set @tStr = substring(@tStr,(len(@target) + 2), ( len(@tStr) - (len(@target) + 1)) )
set @tStr = ltrim(rtrim(@tStr))
end
end
else
begin
set @target = @tStr
set @tInt = convert(int,@target)
select @tInt
set @tStr = ''
end
-- check to exit
if ( len(@tStr) <= 0 )
begin
set @flag = 0
end
end
RESULTS:
-----------
1234
(1 row(s) affected)
-----------
5678
(1 row(s) affected)
-----------
9012
(1 row(s) affected)
-----------
3456
(1 row(s) affected)
-----------
444444
(1 row(s) affected)
October 24, 2008 at 2:17 am
Hi both,
thanks for you time and your solution !
that's run now !
Thanks for all
Christophe
October 24, 2008 at 7:39 am
It's good to have a working solution, but the performance of a WHILE loop construct for splitting strings could have problems with performance when dealing with a large number of records. You may wish to examine the following solution that uses a "tally table". Jeff Moden often prescribes this kind of technique for splitting delimited strings. Check out his posts on "tally table" as well...
--===== Set up the initial string and a loop control variable
DECLARE @tStr AS varchar(4000), @LOOPER int
SET @tStr = '1234,5678,9012,3456,444444'
SET @LOOPER = 1
--===== Create a TALLY table of auxiliary numbers from 1 to 40
DECLARE @TALLY TABLE(N int IDENTITY(1,1) PRIMARY KEY CLUSTERED)
--===== Turn off the "1 row affected" message that will otherwise occur 40 times
SET NOCOUNT ON
WHILE @LOOPER < 40
BEGIN
INSERT INTO @TALLY DEFAULT VALUES
SET @LOOPER = @LOOPER + 1
END
--===== Turn NOCOUNT back off to see all other "rows affected" messages
SET NOCOUNT OFF
--===== Add a leading and trailing comma to accommodate the string splitting technique
SET @tStr = ',' + @tStr + ','
--===== Select the original string, the tally table number, and
--===== the substring details to help show how the technique works
SELECT @tStr AS ORIGINAL, N, SUBSTRING(@tStr,N,LEN(@tStr)-N+1) AS SUBSTR,
CAST(LEFT(SUBSTRING(@tStr,N,LEN(@tStr)-N+1),CHARINDEX(',',SUBSTRING(@tStr,N,LEN(@tStr)-N+1),1)-1) AS int) AS NUMBER
FROM @TALLY
WHERE N < LEN(@tStr) AND
SUBSTRING(@tStr,N - 1,1) = ','
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 24, 2008 at 8:37 am
How about converting the int to a varchar with a leading and trailling comma.
Add a leading and trailing comma to the positions list
and use charindex to see if the int is present.
The extra commas ensures 125 will not match as it has to be ,1251,
If spaces were present, these could be removed using REPLACE
If the nvarchar is ever likely to use more than 3998 characters, it could be converted to varchar.
This can then be used directly in a where clause.
declare @myinteger int
declare @positionslist nvarchar(4000)
select @positionslist= '1251,3352,4194,3367,4501,3372,4193,2109'
select @myinteger = 1251
select 'found it'
where
(
charindex(',' + convert(varchar(12),@myinteger)+ ','
,',' + @positionslist + ',') > 0
)
October 24, 2008 at 4:57 pm
christophe.bernard (10/23/2008)
where condition in (CAST(int), @PostionsList)Christophe
I just went back and re-read this thread, and realized that we might all be overthinking this one. If all you want to do pass that string in and use it in an IN clause, you can do it like this:
DECLARE @PositionsList nvarchar(4000)
SET @PositionsList = '1251,3352,4194,3367,4501,3372,4193,2109'
DECLARE @SQL varchar(4500)
SET @SQL = 'SELECT * FROM MyTable WHERE Condition IN (' + @PositionsList + ')'
--PRINT @SQL
EXEC (@SQL)
If this is something that can be accessed externally (ie. via a website), you do not want to use this method, as it is not secure.
October 25, 2008 at 9:31 am
smunson (10/24/2008)
It's good to have a working solution, but the performance of a WHILE loop construct for splitting strings could have problems with performance when dealing with a large number of records. You may wish to examine the following solution that uses a "tally table". Jeff Moden often prescribes this kind of technique for splitting delimited strings. Check out his posts on "tally table" as well...
--===== Set up the initial string and a loop control variable
DECLARE @tStr AS varchar(4000), @LOOPER int
SET @tStr = '1234,5678,9012,3456,444444'
SET @LOOPER = 1
--===== Create a TALLY table of auxiliary numbers from 1 to 40
DECLARE @TALLY TABLE(N int IDENTITY(1,1) PRIMARY KEY CLUSTERED)
--===== Turn off the "1 row affected" message that will otherwise occur 40 times
SET NOCOUNT ON
WHILE @LOOPER < 40
BEGIN
INSERT INTO @TALLY DEFAULT VALUES
SET @LOOPER = @LOOPER + 1
END
--===== Turn NOCOUNT back off to see all other "rows affected" messages
SET NOCOUNT OFF
--===== Add a leading and trailing comma to accommodate the string splitting technique
SET @tStr = ',' + @tStr + ','
--===== Select the original string, the tally table number, and
--===== the substring details to help show how the technique works
SELECT @tStr AS ORIGINAL, N, SUBSTRING(@tStr,N,LEN(@tStr)-N+1) AS SUBSTR,
CAST(LEFT(SUBSTRING(@tStr,N,LEN(@tStr)-N+1),CHARINDEX(',',SUBSTRING(@tStr,N,LEN(@tStr)-N+1),1)-1) AS int) AS NUMBER
FROM @TALLY
WHERE N < LEN(@tStr) AND
SUBSTRING(@tStr,N - 1,1) = ','
Steve
(aka smunson)
:):):)
Thanks for the "plug", Steve... but, YOWCH! I'd never make a Tally table with a loop.
Please see the following for how to construct a Tally table, how it works, and some of the uses for it...
http://www.sqlservercentral.com/articles/TSQL/62867/
Also, here's and article on how to split single and multi-dimensional parameters using the Tally table...
http://www.sqlservercentral.com/articles/T-SQL/63003/
Seth's method, using dynamic SQL is also pretty good, but you really need to pay attention to his warning about using dynamic SQL, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2008 at 7:04 am
Jeff,
Thanks for acknowledging. The only reason I chose to use a loop is because I just didn't have time to go look up the technique that selects from one of the system databases. Given that the idea was to get just 40 numbers, I figured that any execution time difference was of a size too small to matter as it's a one-time event. I know about the other technique, and as soon as I get it memorized, I promise I'll not use the WHILE loop any more.
As you included detail on HOW to use a tally table, was there anything about my SELECT from it that deviates from the concepts presented?
Seth's warning on dynamic SQL is indeed on the money. The potential for trouble there can be rather alarming.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 27, 2008 at 7:51 am
Hi everybody,
thanks for your posts,
i'd like to have your knowledge !
thanks for all to help me !
christophe
October 27, 2008 at 8:16 pm
smunson (10/27/2008)
Jeff,Thanks for acknowledging. The only reason I chose to use a loop is because I just didn't have time to go look up the technique that selects from one of the system databases. Given that the idea was to get just 40 numbers, I figured that any execution time difference was of a size too small to matter as it's a one-time event. I know about the other technique, and as soon as I get it memorized, I promise I'll not use the WHILE loop any more.
No problem... The only reason why I worry about such things is that someone might see it and think it's ok to use a loop to build it. Lot's of well meaning folks "code by example" without taking the time to understand what the loop does to them. The difference in time for THIS example is small... if they use it somewhere else, the time differences could be quite large especially if they use it in a function that gets used over and over.
As you included detail on HOW to use a tally table, was there anything about my SELECT from it that deviates from the concepts presented?
Well... kind of... your split code is just a little complex. For the size of the example being split, it makes less than a second's difference in a 100,000 row sample compared to the following...
SELECT @tStr AS ORIGINAL, N, SUBSTRING(@tStr,N,LEN(@tStr)-N+1) AS SUBSTR,
CAST(SUBSTRING(@tStr, N+1, CHARINDEX(',', @tStr, N+1)-N-1) AS INT) AS Number
FROM @TALLY
WHERE N < LEN(@tStr) AND
SUBSTRING(@tStr,N,1) = ','
...but when the string to be split gets wider, the time differences start to edge up. For example, if the string is 4 times that of the example, the time difference between the two methods is 6 seconds with the more complex/slower of the two taking 51 seconds.
I'd have to say that for readability and performance, I'd go with the less complex of the two. Of course, you'll need a Tally table a bit wider than 40 characters to test it. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2008 at 9:38 am
Jeff,
Thanks for the feedback. I'll be referring back to your example code now rather than trying to remember it from scratch. There was a time (back in the day), when all I had to do was rewrite code from the photographic memory, but alas, it just doesn't work so well any more...:w00t:
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 28, 2008 at 9:14 pm
Heh... yep... I know exactly what you mean. The good part about a failing memory is all the new people you get to meet everyday. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2008 at 6:41 am
To coin one of your phrases, YOWCH !!! ... at least in terms it getting bad enough to be meeting new people every day. Thank goodness it's not that bad, but it sure is annoying when you grow up with this capability and then learn to RELY on it as much as I have, and then to no longer be able to rely on it is a slightly milder YOWCH !!! And, while memory is the 1st thing to go, I can't remember the 2nd or 3rd... and perhaps that's best, as that way I probably won't recognize the loss...:D:D:D
Steve
(aka smunson)
:):):)
Jeff Moden (10/28/2008)
Heh... yep... I know exactly what you mean. The good part about a failing memory is all the new people you get to meet everyday. 😛
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply