Help please with trim address field.

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help please with trim address field.

Author

Topic

jamesrah
Starting Member

13 Posts

Posted - 2010-05-21 : 06:07:32

Hi,

I have a table containing addresses

example:
100, High Drive, Noddy Farm, Fairy, Post code
100, High Road, Blunk, Fairy, Post code
FLAT 6, New Link Road, Greenland, Fairy, Post code
84, Nortyh Road, Hame, Fairy, Post code

I want to use a Trim / string function to pull out the address between the 2nd , and the 3rd , is there a way to say start at 2nd , and trim end at 3rd ,

Hope this makes sense?

Any help is much appreciated!

Cheers,
James

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-21 : 06:17:12

Use Substring() function.

You expected output will help us!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 07:01:17

What i understand from your post is that complete address is stored in single column and you need the 2nd and 3rd value separated by comma.

If my understanding is correct then try the below example:

Insert into @Address
Select '100, High Drive, Noddy Farm, Fairy, Post code' union
Select '100, High Road, Blunk, Fairy, Post code' union
Select 'FLAT 6, New Link Road, Greenland, Fairy, Post code' union
Select '84, Nortyh Road, Hame, Fairy, Post code'

Select t.Srno, Parsename(addr,3) as col2, ParseName(addr,2) as Col3
from
(
Select Srno, Replace(left(Addr, len(addr)- Charindex(',',reverse(addr))),',','.') as addr
from @Address a
) T

Assumption: There will be no dot present in address.
In case any dot is present,you need to first replace it will some character.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jamesrah
Starting Member

13 Posts

Posted - 2010-05-25 : 08:07:21

Thanks for the help.

I have been using substring however the place names characters vary in length. I am looking at pulling out the place name after the 2nd comma to get the following output:

Noddy Farm
Blunk
Greenland
Hame

The place name between the 2nd and 3rd comma.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-25 : 09:05:58

Try this:
Declare @Address table
(Srno int identity,
Addr varchar(1000))

Insert into @Address
Select '100, High Drive, Noddy Farm, Fairy, Post code' union
Select '100, High Road, Blunk, Fairy, Post code' union
Select 'FLAT 6, New Link Road, Greenland, Fairy, Post code' union
Select '84, Nortyh Road, Hame, Fairy, Post code'

Select t.Srno, ParseName(addr,2) as Col3
from
(
Select Srno, Replace(left(Addr, len(addr)- Charindex(',',reverse(addr))),',','.') as addr
from @Address a
) T

Assumption: There will be no dot present in address.
In case any dot is present,you need to first replace it will some character.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jamesrah
Starting Member

13 Posts

Posted - 2010-05-25 : 09:46:56

Brilliant! Cheers your a wee superstar!!!!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-25 : 09:59:19

Note that if the address has a dot, the above code may give different result

Madhivanan

Failing to plan is Planning to fail

jamesrah
Starting Member

13 Posts

Posted - 2010-05-25 : 10:22:39

Yes when I use the code the commas become fullstops.

jamesrah
Starting Member

13 Posts

Posted - 2010-05-26 : 04:01:27

How can I get this to work without the fullstops?

jamesrah
Starting Member

13 Posts

Posted - 2010-06-01 : 03:55:46

Hi,

Still having issues with this, can't get to work. When I use the code it changes to full stops. Any other suggestions?
I need to extract the data from a address string, I want the part between the 2nd and 3rd comma.

Cheers!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-01 : 05:35:12

Please show us the statement that you are using and what is wrong with the result.
Because if you are retrieving the part between the commas then it shouldn't bother you if they are dots now.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources