How to Compare exact string using INLIST() function

[79251]

How to search for the exact string with INLIST() function, I used SET EXACT ON also but it does't work with the INLIST(). I used,<br>SELECT * FROM EMP where INLIST(Dept, &quot;R1&quot;, &quot;R2&quot;, &quot;R5&quot<br>it returns all the records having starting value as &quot;R&quot;<br>ie all the departments starting with &quot;R&quot;. example R1-R10

 

[foxdev]

What is the value of <i>Dept</i>?&nbsp;&nbsp;I'm not seeing the behavior you mention; for example, even with SET EXACT OFF:<br><br><FONT FACE=monospace>inlist(&quot;R&quot;, &quot;R1&quot;, &quot;R2&quot;, &quot;R5&quot</font><br><br>returns false.&nbsp;&nbsp;Even an empty string for Dept returns false.&nbsp;&nbsp;So we'll have to know more about the values of Dept that are getting included in your result set. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[jonscott8]

Hi 79251,<br><br>&nbsp;&nbsp;Just out of curiousity try changing your where clause to WHERE (INLIST(Dept, &quot;R1&quot;, &quot;R2&quot;, &quot;R5&quot=.T.)<br><br>&nbsp;&nbsp;If by chance, one of your list parameters (&quot;R1&quot;,&quot;R2&quot;,etc.) evaluates to .NULL., then Inlist returns .NULL. instead of .T. or .F.<br><br>&nbsp;&nbsp;I am not aware of how SQL would treat a where clause that returned a .NULL. value. I would assume it would treat it as .F., but you never know.<br><br>Also, are your list parameters hard coded in like in your example? Or are they stored in variables or table fields?<br><br>Jon<br>

 

[79251]

Dear Mr.Robert Brodley<br><br>If you use this function in a SQL statement then only it fails otherwiswe it works fine. So please try the following<br>scenarion.<br>DEPT<br>----<br>R1<br>R2<br>R3<br>R1<br>R<br>R<br>R2<br>R11<br>R3<br><br>The above mentioned are values in a employee table and try to execute the following query.<br><br>SELECT SECTION FROM EMP WHERE INLIST(DEPT,

 

[79251]

Dear Mr.Robert Brodley<br>If you use this function in a SQL statement then only it <br>&nbsp;&nbsp;&nbsp;DEPT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;----&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>1. R1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4. R4&nbsp;&nbsp;&nbsp;&nbsp;7.&nbsp;&nbsp;R3<br>2. R2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5. R&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8.&nbsp;&nbsp;R4<br>3. R1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6. R2&nbsp;&nbsp;&nbsp;&nbsp;9.&nbsp;&nbsp;R1<br><br>The above are records in a employee table.<br>SELECT SECTION FROM EMP WHERE INLIST(DEPT, &quot;R&quot;, &quot;R1&quot<br><br>With result of the above query instead of listing the records having R & R1 it shows all the record.&nbsp;&nbsp;This is the actual problem.

 

[79251]

Dear Mr.John,<br><br>I tried your suggestion, but it doesn't work,&nbsp;&nbsp;as per as inlist parameters I am using both, variable aswell as hardcoding..<br><br>Regards

 

[foxdev]

Pretty wild.&nbsp;&nbsp;Oddly, I get the same behavior using the IN clause, like this:<br><br><FONT FACE=monospace>SELECT SECTION FROM EMP WHERE DEPT IN (&quot;R&quot;, &quot;R1&quot<br></font><br>so it is not unique to the INLIST() function.<br><br>There is the easy solution, and the complex solution.&nbsp;&nbsp;I haven't thought of the complex solution, so lets go with the easy one:<br><br>pad the criteria strings to the length of the underlying column.&nbsp;&nbsp;For example, the following will give you what you want (you can also use INLIST()):<br><br><FONT FACE=monospace>SELECT SECTION FROM EMP WHERE DEPT IN (<font color=red>&quot;R &quot;</font>, &quot;R1&quot<br></font><br><br>Note the trailing space after the R in the first criteria.&nbsp;&nbsp;Therefore, if DEPT has a width of 2, make sure your criteria strings are also a width of 2. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[foxdev]

<b>The complex solution <font color=red>(&quot;the right way&quot</font> discovered</b><br><br><FONT FACE=monospace>SET ANSI ON</font><br><br>This will give you the desired behavior, 79251, without any changes to your SELECT statement. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[79251]

Dear Mr.Robert Bradley,<br><br>Thank you very much for your solution, the second solution I have used and it works fine without any code changes.&nbsp;&nbsp;<br><br>I thank you once again for your efforts in this regard.<br><br>Bye<br><br>Logamoorthy & Our Team<br>