• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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

0
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 3
  • 2
  • 2
  • +1
1 Solution
 
ansudhindraCommented:
try it like...
 
<cfset ids="123456,123454,989789" />
<cfset ids="'"&ids&"'" />
<cfset ids=replace(ids,",","','","all") />
<cfoutput>#ids#</cfoutput>

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
hi,

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
0
 
gdemariaCommented:


<cfset theList = "123456,123454,989789">


 
'#listChangeDelims(theList,"','")#'

Open in new window



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)

0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
gdemariaCommented:
> 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..
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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'',''238413a''))
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Solved using replaceList
0
 
Bhavesh ShahLead AnalysistCommented:
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('238413,238414,238413a',','))
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

Open in new window

0
 
gdemariaCommented:

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''))

Open in new window



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)
           )
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now