SQL Help

[neil3k]

Say I have an address like

54 Solent Gardens

in a column in my database. How would i go about cutting it and move the Solent Gardens bit to a different column?

Halp

 

[SimonCHere]

With great difficulty!
Well if they are all [number] [place] you are ok but what about
Flat 2a Something house

Simon

 

[neil3k]

they are all [number] [place]

however, some numbers can only be one digit i.e

8 test drive

and soem can be

84 test land

 

[GravyMonster]

Very quick and very dirty...

You'll need to create two fields (if you haven't already), one called HouseNumber, the other called StreetName

UPDATE Table SET 
   HouseNumber = LEFT(AddressField, CHARINDEX(' ', AddressField)),
   StreetName = RIGHT(AddressField, LEN(AddressField) - LEN(LEFT(AddressField, CHARINDEX(' ', AddressField))) - 1)

Obviously this won't work for the 'Flat 2a' scenario SimonC mentioned.

 

[neil3k]

Hi

thanks for the help

is it possible to leave house number in the field it exists at the minute and simply cut the sstreet name and move that ?

 

[SimonCHere]

Do it in 2 passes.
Create one new column (say streetname) and run an update to populate that column.
then I think runing the update again on the first column will work:

UPDATE Table SET 
   StreetName = RIGHT(AddressField, LEN(AddressField) - LEN(LEFT(AddressField, CHARINDEX(' ', AddressField))) - 1)

UPDATE Table SET 
   AddressField = LEFT(AddressField, CHARINDEX(' ', AddressField)),
CHARINDEX(' ', AddressField))) - 1)

I think that should work but test it first!