help converting VB function to SQL function

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 2005 Forums

Transact-SQL (2005)

help converting VB function to SQL function

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-12 : 00:19:04

Hey Guys,

I have the following VB.NET function, and am trying to move it to SQL.

any help on converting this is greatly appreciated.... I'm not very good on TSQL, but trying to get better.. any direction is very helpful .. (perhaps theres something built in or a better approach for this ?)

Thanks again!
mike123

Public Function showTitle_PageName(ByVal title As String) As String

title = Trim(title)
title = Replace(title, """, "")
title = Replace(title, "&", "")
'title = Replace(title, """", "")
title = Replace(title, "/", "")
title = Replace(title, "\", "")
title = Replace(title, "'", "")
title = Replace(title, "&", "")
title = Replace(title, ":", "")
title = Replace(title, "*", "")
title = Replace(title, "(", "")
title = Replace(title, ")", "")
title = Replace(title, ".", "")
title = Replace(title, "?", "")
title = Replace(title, "`", "")
title = Replace(title, ";", "")
title = Replace(title, "#", "")
title = Replace(title, "<", "")
title = Replace(title, ">", "")
title = Replace(title, "{", "")
title = Replace(title, "}", "")
title = Replace(title, "%", " percent")

title = Replace(title, "_", "-")
title = Replace(title, " ", "-")

If IsNumeric(title) Then
title = title & "_"
ElseIf title Is Nothing Then
title = title & "-"
End If

showTitle_PageName = title

Return showTitle_PageName

End Function

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-12 : 11:01:51

SQL doesn't have TRIM, you'll have to use RTRIM(LTRIM(FieldName))

Replace is same in TSQL as VB; Replace(StringToBeSearched,StraingToSearch,ReplaceExpression)

You'll have to use

SET @Value = ...

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-12 : 23:33:29

Hi Guptam,

Awesome that is very helpful, I managed to get everything except this part.... any idea on the best way to approach what I have pasted below?

Thanks again!
mike123

If IsNumeric(title) Then
title = title & "_"
ElseIf title Is Nothing Then
title = title & "-"
End If

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 00:42:14

In SQL Server you combine strings using "+" ... in SQL there is IsNumeric function also. But if statement work slightly differently..

If IsNumeric(@Title)
Set @Title = @Title + '_'
Else
Begin
If (@Title Is Null)
Set @Title = '-'
End

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-13 : 01:59:21

Hi Guptam,

much appreciated, but I am getting just 1 small error.. and can't seem to fix it.. this is happening on your posted IF ISNUMERIC section of code.....

any input is very helpful!

Thanks again!
mike123

Msg 4145, Level 15, State 1, Procedure showTitle_PageName, Line 50
An expression of non-boolean type specified in a context where a condition is expected, near 'Set'.
Msg 156, Level 15, State 1, Procedure showTitle_PageName, Line 51
Incorrect syntax near the keyword 'Else'.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 02:06:38

If (IsNumeric(@Title) = 1)
Begin
Set @Title = @Title + '_'
End
Else
Begin
If (@Title Is Null)
Set @Title = '-'
End

Sorry.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-13 : 02:12:36

sorry was just having a little trouble with the syntax, still a little wierd to me but Ill make some sense of it ..

thx again!

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 02:17:45

np ;-).. I just forgot IsNumeric returns a int value and not a true/false. Sorry. Let me know if there are any other issues.. take care.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-13 : 05:59:52

quote:
Originally posted by guptam

np ;-).. I just forgot IsNumeric returns a int value and not a true/false. Sorry. Let me know if there are any other issues.. take care.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Note that isnumeric is not always reliable

select isnumeric('12d1'),isnumeric(','),isnumeric('.')

Madhivanan

Failing to plan is Planning to fail

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-13 : 10:30:08

Here is an attempt at an alternative to ISNUMERIC. It is a procedure which attempts to convert to decimal - if error return 0 else return 1.

CREATE Procedure pIsNumeric(@string varchar(100))
--returns 0 for not numeric, 1 for numeric
------------------------------------------------
/*
USAGE:
declare @Var int
EXEC @Var =[dbo].[pIsNumeric] '12d1'
Select @Var
*/
------------------------------------------------
as
BEGIN
DECLARE @myERROR int
DECLARE @Res int, @Check decimal(10,5)
BEGIN TRY
set @Check= convert(decimal,@string )
set @Res=1
END TRY
BEGIN CATCH
SET @myERROR = @@ERROR
IF @myERROR <>0
set @Res=0
Else
set @Res=1
END CATCH
RETURN @Res

END

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 11:41:10

Thanks guys ;-). Forgot that little bit my bad :(.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources