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 |
|
jarv
Posting Yak Master
131 Posts |
 Posted - 2007-08-01 : 05:34:53
|
in my website a user can select either the first 3 or 4 characters of their postcode I want to match that with the PUBS table rsPostCode field. there is a pub with the starting postcode of WC2H and in teh list is WC2 when a user select WC2 the pub with the postcode WC2H is not added?!here is my code:pSQL = "INSERT INTO favepub (UserID,PubID)"pSQL = pSQL & "SELECT " & session("userID") & ", pubID "pSQL = pSQL & "FROM pubs "pSQL = pSQL & "WHERE left(rsPostCode,4) = '" & Session("rsPostCode") & "'" |
|
|
bienew
Starting Member
8 Posts |
Posted - 2007-08-01 : 05:44:24
|
| WHERE rsPostCode LIKE '" & Left(Session("rsPostCode"),4) & "%'" ? |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-01 : 05:50:05
|
| 1 Avoid using concatenated sql statements2 Always use stored procedure with input parametersMadhivananFailing to plan is Planning to fail |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-08-03 : 15:16:20
|
| The answer is quite simple. WC2H is not = WC2. Left(rsPostCode, 4) where rsPostCode = 'WC2' = 'WC2'.Left() doesn't add anything if the string you pass to it is less than the number of characters you specify, it simply returns the original string.This makes sense if you think about it just a little bit. What would Left() use to add? Spaces? Question marks? Dots? Xs? Zeros? I guess that would depend on what the individual programmer/DBA wanted, wouldn't it? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-08-04 : 06:39:07
|
| why not use patindex to validate character occurrence?--------------------keeping it simple... |
|
|
|
jarv
Posting Yak Master
131 Posts |
Posted - 2007-08-04 : 14:45:37
|
| ok say i have WC2H as a pub postcode and a user selects WC2 as their postcode, it doesn't add that pub with WC2H postcode?! |
|
|
|
|
|
|
WHERE = OR LIKE