how to compare part of the field to a variable

[tixi]

Hi all!<br><br>I've got a problem: I need to compare first 4 letter to another variable in WHERE clause. <br><br>Example: select * from table1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where first 4 letters of Field 1 = variable1<br><br>I tried following SUBSTRING but it did not work. Field 1 is numeric and its length is 10. Variable 1 is char.<br><br>SUBSTRING (FIELD1 AS CHAR(10) FROM 1 TO 4) = VARIABLE1<br><br>What went wrong?<br><br>Thanks for help! <br><br>

 

[carp]

What RDBMS are you using?

 

[tixi]

I've got a bit exotic system, I'm trying to deal with: Non Stop Himalaya (Non Stop SQL/MP), so perhaps the standard SQL would be the best. It has _usually_ worked.

 

[MikeLacey]

Tixi,<br><br>That substr is puzzling me. This is what works in the SQL flavours I'm familiar with<br><br><FONT FACE=monospace><b><br>select hdprsp from cshdprsp where substr(hdprsp,1,2)='20'<br></font></b><br><br>this also works<br><br><FONT FACE=monospace><b><br>select hdprsp from cshdprsp where substr(to_char(hdprsp),1,2)='20'<br></font></b><br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Please -- Don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

 

[bigscouse]

Thr ingres method might apply, here it is: -<br><br>select * <br>from table1<br>where left(char(field1),4) = 'variable1'<br><br> <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>

 

[tixi]

Thanks for your help, and sorry for the delay. I got fed up with the problem and left it be for a while, but couple of days ago I did manage to make it work. The solution was, that I needed to change VARIABLE1 to characters as well.<br><br>So the final solution looks like this:<br>SELECT * FROM TABLE1 WHERE SUBSTRING(CAST(FIELD1 AS CHAR(10)) FROM 1 TO 4) = CAST(VARIABLE1 AS CHAR(4))<br><br>Again, thanks for your help! <br><br>