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 |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2006-05-10 : 03:21:38
|
| Hi,I am writing a sql query for something like a data entry address search.One of the fields is Postcode.What I am trying to get working is the spaces that the users can enter between the postcodes.For example, a postcode in London is like: TW12 YRZNotice that there is a space. But another user may accidentally enter TW1 2YRZI would like the search to return the same result for these two postcodes.This is what i have in the where clause at present but it is not quite right because of the spaces that I mentioned abovewhere ... AND charindex(lower(@c_postcode), lower(c_postcode)) = 1 ... AND ...Any thoughts please?Thanks |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-10 : 03:26:44
|
can you do somthing like this Declare @var1 varchar(20)Declare @var2 varchar(20)Set @var1 = 'TW12 YRZ'Set @var2 = 'TW1 2YRZ'Select Case When Replace(@var1,' ','') = Replace(@var2,' ','') Then 'Code is Same' else 'Code is Different'End If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2006-05-10 : 03:37:33
|
| With your help this seems to be doing the work now.This is the new sql that I have created, thankscharindex(lower(rtrim(ltrim(replace(@c_postcode, ' ', '')))), lower(rtrim(ltrim(replace(c_postcode, ' ' , ''))))) = 1 |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-10 : 03:44:59
|
| I dont think you require rtrim and ltrim since replace will remove all the spaces..If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-10 : 04:14:22
|
| rtrim and ltrim wont remove the spaces that reside between the wordsMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
search sql