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 |
|
jalgoza
Starting Member
2 Posts |
 Posted - 2007-04-05 : 04:06:31
|
| The address field in my User table has data like:ADDRESS=========================1905 1905 S OAK ST 314 ROSSELL AVE 252 252 HIGH MEADOWS ST 1402 1402 TOWNSEND CT 2021 MAHAN AVE 347 347 N 7 AVE As you see we have repeating House #s here. I would like to write a query to:1.Give me addresses and its counts where the house # has been repeated.2.Correct the repeating house # to have a single house # in the Street AddressPlease help me out here. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-04-05 : 05:33:10
|
| 1 Validate data before sending them to table2 Select address from table where substring(address,charindex(' ',address)+1,len(address)) like '%'+substring(address,1,charindex(' ',address)-1)MadhivananFailing to plan is Planning to fail |
 |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-04-05 : 06:23:41
|
| Hi,there is a slight modification place '%' at end of the querySelect address from table where substring(address,charindex(' ',address)+1,len(address)) like substring(address,1,charindex(' ',address)-1) + '%' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-04-05 : 06:33:01
|
quote:
Originally posted by PeterNeo
Hi,there is a slight modification place '%' at end of the querySelect address from table where substring(address,charindex(' ',address)+1,len(address)) like substring(address,1,charindex(' ',address)-1) + '%'
Thanks  MadhivananFailing to plan is Planning to fail |
|
|
|
jalgoza
Starting Member
2 Posts |
Posted - 2007-04-05 : 08:19:18
|
| Thanks Everybody |
|
|
|
|
|
|
|
|
Identifying repeating values in string