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 |
|
bubba
Starting Member
4 Posts |
 Posted - 2003-03-20 : 16:26:56
|
| Hi.I need to run a query that will take a postal code 'a1b 2c3' and give me the results for all records that match the first 3 digits of the code (ie. 'a1b'). Thanks for your help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 16:30:00
|
| SELECT * FROM TABLEWHERE Postal_Code LIKE 'a1b%'Tara |
 |
|
|
bubba
Starting Member
4 Posts |
Posted - 2003-03-20 : 16:32:43
|
| Thanks Tara, but I was looking for a more generic answer. I was only using the 'a1b' as an example. The user will enter the postal code (so it could be anything) and I need to match it with anything in the table that matches only the first 3 letters in the query.Sorry I didn't explain that well enough. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 16:47:37
|
| Assuming your using a sproc:How About: Declare @GoingPostal varchar(5)SELECT * FROM Table where PostalColumn Like SUBSTRING(@GoingPostal,1,3)+'%'Brett8-) |
|
|
|
bubba
Starting Member
4 Posts |
Posted - 2003-03-20 : 20:09:10
|
| I'm almost there. The "+ '%' " doesn't seem to be working. Does '+' work with strings? Does it need to be some sort of concatenate function? |
|
|
|
bubba
Starting Member
4 Posts |
Posted - 2003-03-20 : 20:36:02
|
| GOT IT!!select * from TABLE Where FS_Code like CONCAT(SUBSTRING('$code',1,3),'%')"; |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-20 : 21:41:37
|
| It would help if you told us that you're NOT USING SQL SERVER, since we are a SQL Server site. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-21 : 15:31:19
|
| What is that? Never seen that syntax before.I've seen Access: Like Mid(@GoingPostal,1,3)&'*'SQL Server: Like SUBSTRING(@GoingPostal,1,3)+'%'DB2: Like SUBSTRING(@GoingPostal,1,3)||'%'Oracle: (Well I've forgotten that one...have to look it up)Brett8-) |
|
|
|
|
|
|
|
|
text query