Text select question

[rwies]

I have a database with address, number and street fields. The address field has to contain the full address. The number field needs to contain just the street number. The street field needs to contain the street.

The address field already contains the complete address.

I need to select the first section of characters (from start of field to first space) in my address field to set to the number field. I then need to selct everything after the first space to set to the street field.

Any ideas how I can write the code to make these selections?

Ron--

 

[AlexCuse]

I think you need to reconsider your table design.

And are you serious about this being a TEXT column (are you sure its' not varchar(x))?

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

You'll want to use a combination of Left, Right, and CharIndex.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]VarChar[/color](50)

[COLOR=blue]Set[/color] @Temp = [COLOR=red]'101 N. Main St.'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Left[/color](@Temp, [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]' '[/color], @Temp)), [COLOR=#FF00FF]Right[/color](@Temp, Len(@Temp) -  [COLOR=#FF00FF]Charindex[/color]([COLOR=red]' '[/color], @Temp))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LNBruno]

Did you try doing a SEARCH on this site using keywords like "LEFT, RIGHT, SUBSTRING" ?



< M!ke >
Acupuncture Development: a jab well done.

 

[bborissov]

[COLOR=blue]declare[/color] @Test [COLOR=blue]TABLE[/color] (Address [COLOR=blue]Text[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'22 dddddddddd bbbbbbbbbbb'[/color])
[COLOR=blue]SELECT[/color] Address,[COLOR=#FF00FF]SUBSTRING[/color](Address,1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],Address)-1) [COLOR=blue]as[/color] Test1,
       [COLOR=#FF00FF]SUBSTRING[/color](Address,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],Address)+1,8000) [COLOR=blue]as[/color] Test2  [COLOR=blue]from[/color] @Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rwies]

You are right, it is a varchar field. Sorry for my error.

Ron--

 

[SQLSister]

Not all addresses start with a number. This is a bad idea if you look at a list of addresses. Some start with words such as the company name, some start withthe street name followed by the number and some start with P. O. Box

I would never attempt to divide out the parts of the street address to have number, street, etc.

If you think to store number separately from street name, you are locked into a losing battle with the many variations of how an address is written. And what do you gain from doing this rather than storing the address line as a whole? It's not like anyone has a use for the street number that is separate from the adddress line.

Questions about posting. See faq183-874

 

[rwies]

Thanks to everybody, I have solved the problem with some of the feedback from Tek-Tips.

Ron--