I have a table that looks like this:
|StreetName NR| NR |
|Teststreet 34| 34 |
How can i delete only the number in Streetname when it is the same in NR??
views:
46answers:
1
+1
A:
UPDATE
Addresses
SET
StreetName = LEFT( StreetName, LEN( StreetName ) - LEN ( StreetNo ) )
WHERE
StreetName LIKE '%'+StreetNo
David Hedlund
2010-03-16 09:19:44
@David - maybe add a space after the % - otherwise "Teststreet 134" might end up as "Teststreet 1" for example.
martin clayton
2010-03-16 09:26:09
@martin: yes, that's a good point, thank you. although if the real life scenario that he wants to catch is the case where people fill their street number both in the address field and the number field (so that addres looks weird when concatenated), i don't think there's a real life example of the data you suggested. either people think they're supposed to write their full street address under 'streetname', or they don't. nobody has the street address 'teststreet 134 34', where '134' is part of the street name, and not the number.
David Hedlund
2010-03-16 10:13:28
@David - agree with you - and OP can make any precise adjustment as needed.
martin clayton
2010-03-16 13:41:55