SQL:Casting a String to IDS with IN clause
Op doesn't mention database, so I'll just use SQL Server, because the example SQL in the question looks like TSQL. There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:
"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog
You need to create a split function. This is how a split function can be used:
SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers
that contains rows from 1 to 10,000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Once the Numbers table is set up, create this split function:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
You can now easily split a CSV string into a table and join on it or use it however you need, even from within dynamic sql. Here is how to use it from your question:
UPDATE t
SET Col1=...
FROM dbo.FN_ListToTable(',','7,15,18') dt
INNER JOIN TBL_USERS t ON CAST(dt.value AS INT)=t.id
How to convert varchar to numeric to use in WHERE clause
You should seriously avoid storing CSV data in your SQL tables, because it represents unnormalized data. That being said, there is a way we can write your query even with CSV data, using a slight correction to the data in @string
:
DECLARE @string varchar(25) = '453,454';
SELECT
FirstName + ' ' + LastName Employee
FROM Employee
WHERE ',' + @string + ',' LIKE '%,' + CONVERT(varchar(10), ID) + ',%';
Demo
The trick here is to convert your input CSV 453,454
into this:
,453,454,
Then, we simply search for %,ID,%
in that modified CSV string.
Create an IN clause on the fly
Quoting article Using comma separated value parameter strings in SQL IN clauses:
DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))
SQL Query results WHERE ID IN (@ids) can't convert nvarchar() to int
SQL Server 2016 and later versions
Declare @ids nvarchar(200) = '123,456';
Select *
From Users
Where ID IN ( Select Value from STRING_SPLIT ( @ids , ',' ))
For older versions SQL Server 2005 - 2014
Declare @ids nvarchar(200) = '123,456'; --<-- Comma delimited list of Client Ids
Select *
From Users
Where ID IN (
SELECT CAST(RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS INT) IDs
FROM (
SELECT Cast ('<X>'
+ Replace(@ids, ',', '</X><X>')
+ '</X>' AS XML) AS Data
) AS t CROSS APPLY Data.nodes ('/X') AS Split(a)
)
Convert a string to int using sql query
You could use CAST or CONVERT:
SELECT CAST(MyVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, MyVarcharCol) FROM Table
Prevent mysql casting string to int in where clause
You should be able to avoid this scenario altogether, but if you wanted a workaround you could concat a character to the front of the string, something like:
SELECT delivery_name
FROM orders
WHERE CONCAT('a',orders_id) = CONCAT('a','985225a')
Related Topics
Update a Single Row with T-Sql
The Object Name Contains More Than the Maximum Number of Prefixes. the Maximum Is 3
Select Columns with Particular Column Names in Postgresql
From Keyword Not Found Where Expected (Oracle SQL)
Select * from Table or Select Id,Field1, Field2, Field3 from Table - Best Practice
Ms SQL Server Last Inserted Id
What's the Asymptotic Complexity of Groupby Operation
How to Update a Blob in SQL Server Using Tsql
Dba_Jobs_Running: Table or View Does Not Exist When Trying to Access from Procedure
How to Make a JPA Query with Left Outer Join
SQL Joins: Future of the SQL Ansi Standard (Where VS Join)
How to Update with Inner Join in Oracle
Group by in Postgres - No Equality for JSON Data Type
Query for Searching the Name Alphabetically
How to Implement Logging and Error Reporting in SQL Stored Procedures