| Author |
 Topic  |
|
|
jamesrah
Starting Member
United Kingdom
13 Posts |
 Posted - 05/21/2010 : 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
Flowing Fount of Yak Knowledge
India
1006 Posts |
Posted - 05/21/2010 : 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
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 05/21/2010 : 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. |
Edited by - pk_bohra on 05/21/2010 07:17:36 |
|
|
|
jamesrah
Starting Member
United Kingdom
13 Posts |
Posted - 05/25/2010 : 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
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 05/25/2010 : 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
United Kingdom
13 Posts |
Posted - 05/25/2010 : 09:46:56
|
| Brilliant! Cheers your a wee superstar!!!!!!! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 05/25/2010 : 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
United Kingdom
13 Posts |
Posted - 05/25/2010 : 10:22:39
|
| Yes when I use the code the commas become fullstops. |
|
|
|
jamesrah
Starting Member
United Kingdom
13 Posts |
Posted - 05/26/2010 : 04:01:27
|
| How can I get this to work without the fullstops? |
|
|
|
jamesrah
Starting Member
United Kingdom
13 Posts |
Posted - 06/01/2010 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 06/01/2010 : 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. |
|
|
| |
Topic |
|
|
|
Help please with trim address field.
