split string 3

[craigward]

Hi,

I have a variable that could contain a variable amount of unqueid’s separated by a (,)

I need to check each name against the database and get the username for each individual. The example below won’t work as it’s one string.

wce_user = "1,2,3,4 '  ‘This could be any number of unique id’s

qry_user = "SELECT * FROM wces_users WHERE uniqueid = '"&(wce_user)&"'"

This above example would be useless as it would look like.

SELECT username FROM wces_users WHERE uniqueid = ' 1,2,3,4’

What is need is:

SELECT username FROM wces_users WHERE wce_uid = ' 1 ' OR wce_uid = '2' wce_uid = '3' etc, etc.

If someone could point me in the right direction of breaking the string up to help build my SQL query it would be most appreciated. Thanks in advance.

 

[guitarzan]

wce_user = "1,2,3,4" 
qry_user = "SELECT * FROM wces_users WHERE uniqueid IN (" & wce_user & ")"

 

[ggriffit]

guitarzan, your answer is the same one I was heading to as well, although in some of the systems I work with I have many values which could be passed in and Oracle has a limit of 1000 items in an IN clause, so if there are lots then it could be a problem,

http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

has some solutions among other sites.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[wvdba]

each_user = split(wce_user, ",")
for i = 0 to ubound(each_user)
qry_user = "SELECT * FROM wces_users WHERE uniqueid =
each_user(i)
'put the result somewhere or do something with it.
next

 

[vicvirk]

wvdba's solution would work,but you would be making a call to the DB for each value...could cause overload if you have over 1000 records, as you stated you might.

Here's how you can build your SQL statement with each value in the CSV string

<%
	strUser = "1,2,3,4"
	raUser = split(strUser,",")
	sqlStart = "SELECT username FROM wces_users WHERE " ' don't forget the space after "WHERE "
	sqlWhere = ""
	for ndx = 0 to ubound(raUser)
		if sqlWhere <> ""  then sqlWhere = sqlWhere & " OR " end if
		sqlWhere = sqlWhere & "wce_uid = '" & raUser(ndx) & "'"
	next
	erase raUser
	sqlFinal = sqlStart & sqlWhere
	response.write sqlFinal
%>

as long as there isn't a limit to the number of "OR" clauses you can insert in the SQL statement, this should work for you and you'll only need 1 db call to execute the full query.

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[craigward]

Great, Thank you for all your help. I have it working now

 

[vicvirk]

Great, Thank you for all your help. I have it working now

Could you post your solution, as others may also find it helpful.

Thanks!


--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[craigward]

This is what i used in the end. The IN is a very powerful feature that i had never used before. But i also tried your example. Thanks for all your help, makes developing much more fun when there are so many helpful people.

wce_user = "1,2,3,4" 
qry_user = "SELECT * FROM wces_users WHERE uniqueid IN (" & wce_user & ")"

but i tried this and it is also very helpful

<%
    strUser = "1,2,3,4"
    raUser = split(strUser,",")
    sqlStart = "SELECT username FROM wces_users WHERE " ' don't forget the space after "WHERE "
    sqlWhere = ""
    for ndx = 0 to ubound(raUser)
        if sqlWhere <> ""  then sqlWhere = sqlWhere & " OR " end if
        sqlWhere = sqlWhere & "wce_uid = '" & raUser(ndx) & "'"
    next
    erase raUser
    sqlFinal = sqlStart & sqlWhere
    response.write sqlFinal
%>