Coast Line
asked on
List Issues
Maybe i worked a bit i do not know
but last 1 hr, i am stuck @ this point
My IDs come as:
123456,123454,989789
Now i want to make them as:
NewID = '123456','123454','989789'
Me using listQualify function to add a single quotes to them and passing as:
'ListChangeDelim(NewID,"|" )'
Either i remove qutes or apply quotes around listchangedelim it is throuwing an error when it interact with the database with some invalid list
but last 1 hr, i am stuck @ this point
My IDs come as:
123456,123454,989789
Now i want to make them as:
NewID = '123456','123454','989789'
Me using listQualify function to add a single quotes to them and passing as:
'ListChangeDelim(NewID,"|"
Either i remove qutes or apply quotes around listchangedelim it is throuwing an error when it interact with the database with some invalid list
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<cfset theList = "123456,123454,989789">
'#listChangeDelims(theList,"','")#'
But the bigger question is why? Since all the values are numeric, you don't need to put them in quotes when used in your query... it is ok to do this.... WHERE THE_ID in (1,2,3,4,5)
> Either i remove qutes or apply quotes around listchangedelim it is throuwing an error when it interact with the database with some invalid list
Please show the code..
Please show the code..
ASKER
using the above first solution, i am getting this in query which is throwing an error
WHERE (va.PID IN (238413,238414,238413a)
OR va.ID IN (''238413'',''238414'',''2 38413a''))
WHERE (va.PID IN (238413,238414,238413a)
OR va.ID IN (''238413'',''238414'',''2
ASKER
Solved using replaceList
Hi,
This post could be out of way to Question still I'm using this one....so sharing...
What we do....
create below function in database.
and calling this way.
va.PID IN (select items from dbo.FN_GENERATETABLE('2384 13,238414, 238413a',' ,'))
This post could be out of way to Question still I'm using this one....so sharing...
What we do....
create below function in database.
and calling this way.
va.PID IN (select items from dbo.FN_GENERATETABLE('2384
GO
CREATE FUNCTION [dbo].[FN_GENERATETABLE]( @STRING VARCHAR(8000),@DELIMITER CHAR(1) )
RETURNS @RESULTS TABLE (ITEMS VARCHAR(8000))
AS
BEGIN
--IF @STRING IS NULL OR @STRING='' RETURN
SET @STRING = LTRIM(RTRIM( @STRING ))
DECLARE @INDEX INT
DECLARE @SLICE VARCHAR(8000)
DECLARE @SLICEPER VARCHAR(8000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@DELIMITER,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @RESULTS(ITEMS) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
You code is showing duplicate single quotes, you only want one single quote
WHERE (va.PID IN (238413,238414,238413a)
OR va.ID IN (''238413'',''238414'',''238413a''))
You can remove ALL single quotes, as I said, these are numbers.
WHERE (va.PID IN (238413,238414,238413a)
OR va.ID IN (238413,238414,238413a)
)
you using ms sql or mysql.
if you using ms sql then i have better suggestion, because even after putting single quote, you need to create dynamic query which is too bit complex for debugging.
- bhavesh