Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! I have a problem with my subfield function even after browsing through the forum. I hope you can help me.
I have a column with addresses which looks like this:
somestreet 123, city but sometimes it will look like this:
some street name 123, city
Now what I want to do is to get rid of the number so in the end my rows will look like:
somestreet, city
some street name, city and so on.
I have tried nesting my subfields but I cannot seem to get rid of the number. The problem is the various amount of blank spaces in the street name, sometimes there is none, sometimes there is 1 or more.
The furthest I have come is this:
SubField(SubField([FieldName], ' ', 1), ',', 1) & SubField([FieldName], ',', 2) AS [FieldName] but that of course results in the street names with spaces to be cut off and only show the beginning.
'somestreet123, city' works fine and becomes 'somestreet city' BUT
'some street name 123, city' becomes 'some city' which is wrong.
Any ideas?
Thanks a lot in advance!
You can make use of the PurgeChar()-function to first get rid of the numbers. Then you can split the fields into two by using SubField() and lastly you can again use PurgeChar()-function to get rid of the spaces in the first field.
maybe
purgechar(SubField(FieldName, ',',1),'0123456789') & ' ' & SubField(FieldName, ',',2)
maybe just
PurgeChar(YourField,'0123456789')
could be enough?
regards
Marco
Hello again everyone and thank you for your input MarcoWedel petter-s
Your solution got me very close although there is one problem left. Some of the house numbers actually have a letter in them (sorry for not specifying that earlier). So they are like:
some street name 123A, city
but if I put the letter into the purgechar then of course all streets that start with that letter also disappear.
Is there a way to specify the purgechar with something like
"if(house number contains a letter, purgechar([field], '0123456789ABC'), purgechar([field], '0123456789'))" ?
Hi,
maybe like this:
tabAddress:
LOAD Replace(Concat(Address,' ',IterNo),' ,',',') as Address
Where not FindOneOf(Address,'0123456789')
Group By RecNo;
LOAD SubField(Replace(Address,',',' ,'),' ',IterNo()) as Address,
RecNo() as RecNo,
IterNo() as IterNo
Inline [
Address
"somestreet 123, city"
"some street name 123, city"
"some different street name 123A, city"
"some other street name 123B, city"
]
While IterNo()<=SubStringCount(Replace(Address,',',' ,'),' ')+1;
hope this helps
regards
Marco