Strip out the Number (House) - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

Strip out the Number (House)

Author

Topic

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-18 : 05:15:29

Can anyone make a suggestion here for my Active X Script in DTS ?

Basically I have a second destination column Called Building_Number.

What I would like to do is strip out the Building number from the Address line one and add to a building number.

The rules would be something like, if the first word of the address line 1 is less that 5 digits and it starts with a number then put the first word into Building Number and but the 2nd and subsequent words into Address Line 1

Function Main()
if (trim(DTSSource("PAD_ADDRESS1")) = ".") Then
DTSDestination("Address_1") = ""
Else
DTSDestination("Address_1") = DTSSource("PAD_ADDRESS1")
End if

Main = DTSTransformStat_OK
End Function

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-18 : 05:33:53

can you give few examples of records?

Karunakaran

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-18 : 09:42:30

1 high street
202 the street
1a sweet street west
the street
.
the house

Does that help ?

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-22 : 02:01:12

quote:
Originally posted by DaveRolph

1 high street
202 the street
1a sweet street west
the street
.
the house

Does that help ?

Is this what you are looking for? Might not be an elegant solution I guess..


        Dim Address_1,SpaceLocation,FirstWord
	Address_1 = "202ad the street"
	SpaceLocation = INSTR(Address_1," ")
	
	if SpaceLocation <= 6  then

	FirstWord = Left(Address_1,SpaceLocation)

	BuildingNumber = Trim(FirstWord)
	Address_1  = Mid(Address_1,SpaceLocation)
	Else
	BuildingNumber = ""
	Address_1  = Address_1
	End If

	MsgBox BuildingNumber
	MsgBox Address_1

Thanks

Karunakaran

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-26 : 00:07:25

Karunakaran

Thank you very much for this it does help me understand the VB Syntax, I have written this function in other languages before and this is not far away.

I think the only problem with this is that it does not check if the first character is a number.

In the example of "The House", the Building_Number should be blank and the Address_1 should be "The House".

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-26 : 11:00:32

DaveRolph,

U gave ur sample data. It would be easy if u provide the expected results in each column (Building_No and Address_Line_1)

U can check whether the first character is numeric by

If isNumeric(left(Address_1),1) Then 
 ....

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-26 : 22:29:54

quote:
Originally posted by DaveRolph

I think the only problem with this is that it does not check if the first character is a number.

Sorry Dave forgot that point

This should work as you expect


Dim Address_1,SpaceLocation,FirstWord
	Address_1 = "202ad the street"
	SpaceLocation = INSTR(Address_1," ")
	
	if SpaceLocation <= 6  then

	FirstWord = Left(Address_1,SpaceLocation)
	
		if (IsNumeric(Mid(FirstWord,1,1))) Then
			BuildingNumber = Trim(FirstWord)
			Address_1  = Mid(Address_1,SpaceLocation)
			MsgBox ("Numeric")
		Else
			BuildingNumber = ""
			Address_1  = Address_1
			MsgBox ("Not Numeric")
		End If
	End If

Thanks

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources