translate vbcode to stored procedure

[Machiaveli]

hello,

Can someone translate the following code into an stored procedure? I have not yet enough skills to do that. I can learn from it if i have an example like this code:

Function splitnumberandadres(address As String, Optional zeroforaddress As Boolean = False) As String
Dim strFree As String, I As Byte

I = Len(address)

Do Until IsNumeric(Mid(address, I, 1)) = True
strFree = Mid(address, I, 1) & strFree
I = I - 1
If I = 0 Then
strFree = ""
GoTo next
Exit Do
End If
Loop

Do Until IsAlphaChar(Mid(address, I, 1)) = True Or Mid(address, I, 1) = "."
strFree = Mid(address, I, 1) & strFree
I = I - 1
If I = 0 Then
Exit Do
End If
Loop

next:

If zeroforaddress = False Then
huisnummeradreslos = Trim(Left(address, Len(address) - Len(strFree)))
Else
huisnummeradreslos = Trim(strFree)
End If

End Function

Function IsAlphaChar(strChar As String) As Boolean
IsAlphaChar = IsCharAlpha(Asc(strChar))
End Function

 

[NoCoolHandle]

cool a free source of workers! what a great idea...

So what is the goal of the code?

I did try a cut and paste but it didn't run as it was missing a few other pieces..the return was always blank...

 

[Machiaveli]

The code will split the address in streetname and housenumber.

you can also use it in a query. use 2 columns and add 0 or 1 in the zeroforaddress section.

 

[NoCoolHandle]

Opps must have forgotten to send this...

Try.. (it isn't 100% but it should give you some ideas...

Use northwind
go

 Create Function ReturnStreet
(@input  varchar(3000))
returns varchar(3000)
as
Begin
declare @num varchar(300)
declare @street varchar(300)
declare @split int
declare @len int
declare @tChar char(1)

declare @revinput  varchar(3000)
set @revinput  = reverse(@input)
set @len = len(@input)
declare @p int
set @p = 1
while @p < len(@input) + 1
	begin
		set @tchar = substring(@revinput,@p,1)		
		if @tchar like '[0-9]' 
			Begin
				if @p < 3 
					begin
						set	@p=@p--do nothing
					end
				else if substring(@revinput,@p-3,3) = ' ht' or   substring(@revinput,@p-3,3) = ' ts'
					begin
						-- ok where does the street start?
						set @p = charindex(' ',@input , len(@input)-@p)
						SET @street = substring(@input, len(@input) - (@p+1) ,@p)
						goto x
					end
				else
					begin
						set @street = substring(@input, len(@input) - (@p -3) ,@p)
						goto x
					end
			end
		set @p = @p+1
	end
set @street = @input
x:
return @street
end

go
Create  Function ReturnNumber(@input varchar(3000))
returns varchar(3000)
as
begin
declare @num varchar(300)
declare @street varchar(300)
declare @split int
declare @len int
declare @tChar char(1)

declare @revinput  varchar(3000)
set @revinput  = reverse(@input)
set @len = len(@input)
declare @p int
set @p = 1
while @p < len(@input) +1
	begin
		set @tchar = substring(@revinput,@p,1)		
		if @tchar like '[0-9]' 
			if substring(@revinput,@p-3,3) = ' ht' or   substring(@revinput,@p-3,3) = ' ts'
				begin
					-- ok where does the street start?
					set @p = charindex(' ',@input , len(@input)-@p)
					SET @num = left (@input, len(@input) - (@p+2))
					goto x
				end
			else
				begin
					set @num =  left(@input,len(@input) - @p + 1) 
					goto x
				end			
		set @p = @p+1
	end
x:
return @num
end

go
create  proc ptest
@address varchar(3000)
as
select dbo.ReturnNumber(@address),dbo.ReturnStreet(@address),@address-- from northwind.dbo.employees
go
select dbo.ReturnNumber(address),dbo.ReturnStreet(address),address from northwind.dbo.employees