Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rsturner82
Starting Member
2 Posts |
 Posted - 2009-10-28 : 07:42:18
|
| I have the following select statement in my vb.net program to retrieve data from an sql data base:Dim sqlCommand As New SqlCommand("SELECT CUST_R.Ref, DEFSUM_R.DSumDesc, CONVERT(VARCHAR(10), DEFSUM_R.DSumDate, 103)as DSumDate, DEFSUM_R.DSumAmt from CUST_R INNER JOIN DEFSUM_R ON DEFSUM_R.GodebtRef = CUST_R.GodebtRef AND DEFSUM_R.index_value = CUST_R.index_value AND DEFSUM_R.export_no = CUST_R.export_no WHERE CUST_R.Ref = '" & Reference & "'", con)The "Reference" is a variable which comes from a textbox which is entered by the user. What i need to do is only search on the first 6 characters of CUST_R.Ref in the WHERE statement.i.e the CUST_R.Ref will be 123456/ww but the varibale reference which the user enters will only be 123456, but i need these 2 to match so how do i tell the WHERE statement to only look at the first 6 characters of CUST_R.Ref.Thanks |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-10-28 : 07:57:15
|
| WHERE LEFT(CUST_R.REF,6) = @ReferenceJimEveryday I learn something that somebody else already knew |
 |
|
|
rsturner82
Starting Member
2 Posts |
Posted - 2009-10-28 : 08:21:32
|
| Great thanks, worked a treat. I knew there was a simple answer but couldnt getin quite right. Thanks |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-28 : 08:36:33
|
| Also tryWHERE CUST_R.REF like @Reference+'%'MadhivananFailing to plan is Planning to fail |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-10-29 : 20:35:17
|
| You probably should look at madhivanan's solution. The other solution will work on smaller sets of data, but on larger sets where you have an index on that column - the index will not be used and performance can suffer. |
|
|
|
|
|
|
WHERE Statement - First 6 characters