--Problem: converting AA to int type failed
String aa= "3,5,11,56,88,45,23";
SELECT * from Erpbumen where ID in (AA);
--Method
SELECT * from Erpbumen where charindex (', ' +ltrim (ID) + ', ', ', ' + '3,5,11,56,88,45,23' + ', ') >0
LTrim (RTrim (m.cust_id))
Remove left and right spaces in the cust_id field
CHARINDEX
Returns the starting position of the specified expression in the string.
Grammar
CHARINDEX (expression1, expression2 [, Start_location])
Parameters
Expression1
An expression that contains the order of the characters to look for. Expression1 is an expression of a short character data type classification.
Expression2
An expression, typically a column that is used to search for a specified sequence. Expression2 belongs to the string data type classification.
Start_location
The starting character position when searching for expression1 in expression2. If there is no given start_location, but a negative number or 0, the search begins at the beginning of the expression2.
return type
Int
Comments
If one of the expression1 or expression2 belongs to the Unicode data type (nvarchar or nchar) and the other does not, the other is converted to a Unicode data type.
If one of the expression1 or expression2 is a null value, CHARINDEX returns a null value when the database compatibility level is 70 or greater. When the database compatibility level is 65 or less, CHARINDEX returns a null value only if both expression1 and expression2 are null.
If expression1 is not found within Expression2, CHARINDEX returns 0.
Example
The first code example returns the position where the sequence "wonderful" begins in the notes column of the titles table. The second example uses the optional start_location parameter to start looking for "wonderful" from the fifth character in the Notes column. The third example shows the result set when expression1 is not found within expression2.
Use pubs
GO
SELECT CHARINDEX (' wonderful ', notes)
From titles
WHERE title_id = ' TC3218 '
GO
--Use the optional start_location parameter to start searching
--for wonderful starting with the fifth character in the notes
--column.
Use pubs
GO
SELECT CHARINDEX (' wonderful ', notes, 5)
From titles
WHERE title_id = ' TC3218 '
GO
Here is the result set for the first query and the second query:
-----------
46
(1 row (s) affected)
Use pubs
GO
SELECT CHARINDEX (' wondrous ', notes)
From titles
WHERE title_id= ' TC3218 '
GO
The following is the result set.
-----------
0
(1 row (s) affected)
Convert varchar value ' 1,2,3,4,5,6 ' to data type int in SQL