CODEWITHSUNDEEP
arrayssql-servercastingintegervarchar
AlIon
AlIon

Reputation: 367

How to cast varchar with multiple values to int

There is a SQL template, which has a ids variable:

select file_name 
from hist_payout 
where hist_payout_id in (:ids)

ids is a varchar, looks like '3, 17, 2001', and the error I get is

Error converting data type varchar to bigint

Is it possible to cast varchar to int array in SQL?

Upvotes: 1

Views: 1086

Answers (2)

marc_s
marc_s

Reputation: 754963

If you're using a recent enough version of SQL Server, you could use STRING_SPLIT to break this @varchar value apart.

Try this:

DECLARE @input VARCHAR(20) = '3, 17, 2001';

SELECT
    CAST(TRIM(value) AS INT)
FROM
    STRING_SPLIT(@input, ',')

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Depending on your database and how you are querying it, there are probably better ways to pass a list of values into a query.

But given a query of the form you specify and assuming standard SQL syntax, you can use LIKE:

where ', ' || :ids || ', ' like '%, ' || cast(hist_payout_id as varchar(255)) || ', %'

I do want to emphasize that there are probably better methods, depending on your environment.

Upvotes: 0

Related Questions