SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help please with trim address field.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamesrah
Starting Member

United Kingdom
13 Posts

Posted - 05/21/2010 :  06:07:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 05/21/2010 :  06:17:12  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Use Substring() function.

You expected output will help us!

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

http://senthilnagore.blogspot.com/
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 05/21/2010 :  07:01:17  Show Profile  Reply with Quote
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.

Edited by - pk_bohra on 05/21/2010 07:17:36
Go to Top of Page

jamesrah
Starting Member

United Kingdom
13 Posts

Posted - 05/25/2010 :  08:07:21  Show Profile  Reply with Quote
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.

Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 05/25/2010 :  09:05:58  Show Profile  Reply with Quote
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.
Go to Top of Page

jamesrah
Starting Member

United Kingdom
13 Posts

Posted - 05/25/2010 :  09:46:56  Show Profile  Reply with Quote
Brilliant! Cheers your a wee superstar!!!!!!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 05/25/2010 :  09:59:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Note that if the address has a dot, the above code may give different result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jamesrah
Starting Member

United Kingdom
13 Posts

Posted - 05/25/2010 :  10:22:39  Show Profile  Reply with Quote
Yes when I use the code the commas become fullstops.
Go to Top of Page

jamesrah
Starting Member

United Kingdom
13 Posts

Posted - 05/26/2010 :  04:01:27  Show Profile  Reply with Quote
How can I get this to work without the fullstops?
Go to Top of Page

jamesrah
Starting Member

United Kingdom
13 Posts

Posted - 06/01/2010 :  03:55:46  Show Profile  Reply with Quote
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!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 06/01/2010 :  05:35:12  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000