did. name FID
-------------------------------------------
1 Abu,def,xyz 233,344,455
Now I want to split these fid and name from table2 in store procedure like this:
ID name FID
---------------
1. Abc. 233
2. Def. 344
3 xyz. 455
Update sp
Query:
Create procedure as splitdata as As Begin Declare @fid varchar (500) Select name, select item as fid from spiltstring(@fid,',')) from table1 Inner join table2 on table1.fid = table2.fid
This shows error
Conversion failed when converting the varchar value '233,344,455' to data type int
Conversion failed when converting the varchar value '233,344,455' to data type int
According to this error message, as far as I know, you could try to use replace method to convert'233,344,455'to INT type, it can replace ',' to blank, then convert it to int type.
Sample code:
SELECT CAST(REPLACE('233,344,455',',','') as int)
Best Regards,
Eric Du
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
239 Points
1069 Posts
values split in sql
Jan 01, 2017 08:27 AM|Capricon User|LINK
Table 1
Tid. name fid
-----------------
1. Abc. 233
2. def. 344
3. xyz. 455
Table 2
did. name FID
-------------------------------------------
1 Abu,def,xyz 233,344,455
Now I want to split these fid and name from table2 in store procedure like this:
ID name FID
---------------
1. Abc. 233
2. Def. 344
3 xyz. 455
Update sp
Query:
Create procedure as splitdata as As Begin Declare @fid varchar (500) Select name, select item as fid from spiltstring(@fid,',')) from table1 Inner join table2 on table1.fid = table2.fid
This shows error
Conversion failed when converting the varchar value '233,344,455' to data type int
How can I do this?
Member
239 Points
1069 Posts
Re: values split in sql
Jan 01, 2017 06:37 PM|Capricon User|LINK
Contributor
6680 Points
2715 Posts
Re: values split in sql
Jan 03, 2017 06:41 AM|Eric Du|LINK
Hi Bakhtawar Ashiq,
According to this error message, as far as I know, you could try to use replace method to convert '233,344,455' to INT type, it can replace ',' to blank, then convert it to int type.
Sample code:
SELECT CAST(REPLACE('233,344,455',',','') as int)Best Regards,
Eric Du
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.