0

Pardon me, I was trying hard to find the answer, but most of the questions are related in the forum related to converting one value, not the whole set.

I am trying to pass the subquery values to the main query but the subquery returning varchar and the main query column is accepting smallint. I tried cast and convert but didn't help me.

select time_off_type_no 
from schedtime 
where activity_no in (select AT_NUMBERS from ACTIVITY where AT_ID = 105)

This query is throwing the following exception

Conversion failed when converting the varchar value '483,484,485,486,487,488,489' to data type smallint

Any advice on how to convert the values much appreciated.

Following query returning '483,484,485,486,487,488,489' and I want to convert all the values to SmallInt or int to pass it to the main query.

select AT_NUMBERS 
from ACTIVITY 
where AT_ID = 105
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Usher
  • 2,146
  • 9
  • 43
  • 81
  • 1
    Search for splitting comma delimited lists into multiple values – MatBailie Nov 11 '18 at 18:44
  • @MatBailie, all i want '483,484,485,486,487,488,489' to 483,484,485,486,487,488,489 because i am passing this value as int in the main query like "where activity_no in (483,484,485,486,487,488,489 )" – Usher Nov 11 '18 at 18:51
  • Yeah, and you Have to turn that SINGLE STRING into a list of MANY INTEGERS... – MatBailie Nov 11 '18 at 20:24
  • Thanks all , as you guys advised i pass the string to function and split the values and return all the values back as int, that worked well – Usher Nov 12 '18 at 15:29

2 Answers2

1

Please try nested casting like:

SELECT CAST(CAST(AT_NUMBERS AS DECIMAL) AS SMALLINT) from ACTIVITY where AT_ID=105

EDIT: Since the returned value is a comma-delimited string, I think this would help if the version of SQL Server is at least 2016

;with cte (ID) as (
  Select string_split (AT_NUMBERS,',') as ID
  from ACTIVITY
  where AT_ID=105
)
select time_off_type_no from schedtime where activity_no in (
   SELECT CAST(CAST(ID AS DECIMAL) AS SMALLINT) from cte
)

If SQL SERVER version is below 2016, we'll need to develop our own split function. You can find examples in How to split a comma-separated value to columns

Try this as an example if so, working in Sql Server 2008:

DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')


SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Ref: https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • You should really declare you scale and precision for your `decimal` datatype. – Thom A Nov 11 '18 at 18:28
  • 2
    It's a comma delimited list, this won't help. – MatBailie Nov 11 '18 at 18:43
  • @MatBailie, that's correct it's comma delimited list. – Usher Nov 11 '18 at 18:45
  • @Eray Balkanli,it won't help because it's a comma delimited list.If it's in front end i can convert but i am not sure what's the best practice in the sql server side – Usher Nov 11 '18 at 18:47
  • @Eray Balkanli, i am using SLQ 2012,do you know which split function I need to use the one your Ref? – Usher Nov 11 '18 at 19:06
  • I would use the one I copied at the bottom of my answer, from the second reference. I'd create a temptable by using it instead of CTE, and use the temp table – Eray Balkanli Nov 11 '18 at 19:07
1

I think you need to split the string by comma if these (483,484,485,486,487,488,489) are individual numbers. If this is whole integer value, not even Big Int limit is like this.

See MS documentation:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

If your SQL server version is more than 2016 or more, then you can use string_split function in this way.

--Use try_cast or Try_convert to avoid any conversion error as well. 

select Try_cast(value as int) Integervalue  from  string_split ('483,484,485,486,487,488,489',',')

Output: 

Integervalue
483
484
485
486
487
488
489

--this will work if it is pure integer value, else it needs to be converted to decimal.

Please make sure to use cross apply if you are using against tables.

If it is less than 2016, you might have to build one string split function as mentioned here.

Splitting the string in sql server

Avi
  • 1,795
  • 3
  • 16
  • 29