Tpaul_10
asked on
Convert function help
Experts,
Here is my code
declare @ItemIDList varchar(8000)
set @ItemIDList = '274686,275521,275522'
select * From MyDb..MyTable where ItemID in (convert(int,@ItemIDList ))
Getting the following error.
Conversion failed when converting the varchar value '274686,275521,275522' to data type int.
Could you please help me correct if I am missing something.
Thanks in Advance.
Here is my code
declare @ItemIDList varchar(8000)
set @ItemIDList = '274686,275521,275522'
select * From MyDb..MyTable where ItemID in (convert(int,@ItemIDList ))
Getting the following error.
Conversion failed when converting the varchar value '274686,275521,275522' to data type int.
Could you please help me correct if I am missing something.
Thanks in Advance.
ASKER
Basically I have a list of ItemIDs with comma delimited and based on the ItemIDs I need to get some information for the ItemIDs from my database table and display some information.
Hope this clarifies and please let me know if you need any further information.
Thanks
Hope this clarifies and please let me know if you need any further information.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Tpaul_10's comment #a39749175
for the following reason:
A
Accepted answer: 0 points for Tpaul_10's comment #a39749175
for the following reason:
A
In order to get this done you need to follow the below steps
Step 1: Create the below split function in your database
Step 2: re-write your query as below
Step 1: Create the below split function in your database
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
DECLARE @index INT
SET @index = Charindex(@SplitOn,@RowData)
While (@index>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))
Set @RowData = Substring(@RowData,@index+1,len(@RowData))
Set @Cnt = @Cnt + 1
SET @index = Charindex(@SplitOn,@RowData)
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
Step 2: re-write your query as below
select * From MyDb..MyTable where ItemID in ( SELECT CAST(DATA as BIGINT) FROM dbo.split(@ItemIDList) )
There is no proper reason why this guy wants to close this question, although now we provided him a better solution.
Can you please elaborate rather than simply typing A
Can you please elaborate rather than simply typing A
ASKER
As I have specified I found a way to get through this issue and I have posted my code as well and your comment came in after that.
I have tested my code and is working as I am expected and that was the reason I wanted to close it.
Thanks and sorry if there is any confusion
I have tested my code and is working as I am expected and that was the reason I wanted to close it.
Thanks and sorry if there is any confusion
Thats Ok, no problem O.
'274686,275521,275522' is not an integer. What are you trying to do?
Kent