Creating a Function in SQL - 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

Transact-SQL (2000)

Creating a Function in SQL

Author

Topic

rfedeleo
Starting Member

2 Posts

Posted - 2003-07-24 : 10:46:26

Hello,
We have a field in our SQL database with information such as the following.

Deny tcp src outside:4.43.128.129/1323 dst inside:4.43.127.81/80 by access-group "100"

We want to extract the ip adderss that comes right after the word "outside" and before "/". We need to create a function to extract this information. We used a VB module in Access to accomplish this but now we are trying to create a function in SQL to do the same. We are not sure how to use the CREATE FUNCTION statement. The following is the VB code we used in Access. Is there a way to create this function while using the existing code we already have. The Access code is pasted below. Thanks.

Option Compare Database

Function IPAddrOut(msgtxt As Variant) As String
Dim temptxt As Variant
Dim istart As Integer
Dim ifin As Integer
Dim lenght As Integer

istart = InStr(1, msgtxt, "outside", vbBinaryCompare)
ifin = InStr(1, msgtxt, "/", vbBinaryCompare)
istart = istart + 8

If istart <> 0 Then
If ifin <> 0 Then
If istart < ifin Then
lenght = ifin - istart
IPAddrOut = Mid(msgtxt, istart, lenght)
Else
IPAddrOut = "N/A"
End If
End If
End If

End Function

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 11:31:15

I'm scrubbing up...I can't get the SELECT TO work...don't know why

but something like:


CREATE FUNCTION udf_GetIP
	(@str varchar(8000))
RETURNS varchar(255)
AS
BEGIN
	DECLARE @IP varchar(255), @x Int, @y Int
	SET @x = CHARINDEX('outside:',@str, 1)+8
	SET @y = CHARINDEX('/', @str,1)-@x
	SET @IP = SUBSTRING(@str, @x, @y)
	RETURN @IP
End
GO

CREATE TABLE #bk_temp(col1 varchar(8000))
GO

INSERT INTO #bk_temp(col1) SELECT 'Deny tcp src outside:4.43.128.129/1323 dst inside:4.43.127.81/80 by access-group "100" '
GO

SELECT dbo..udf_GetIP(col1) FROM #bk_temp
GO

DROP TABLE #bk_temp
GO

DROP FUNCTION udf_GetIP
GO

What am I doing wrong?

Scrub boy

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-24 : 11:39:29

SELECT dbo.udf_GetIP(col1) FROM #bk_temp
:).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 11:54:47

OH MY GOD

WHAT A SCRUB!

Thanks Nigel....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources