VB to SQL Code Required - 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)

VB to SQL Code Required

Author

Topic

harrisw48
Starting Member

34 Posts

Posted - 2005-10-31 : 08:17:19

I have the code below that I need to transfer to SQL.
Can anyone help?

Public Function Strip(ByVal strMessage As String) As String

Dim intCounter As Integer

For intCounter = 1 To Len(strMessage)

letter = Asc(Mid(strMessage, intCounter, 1))

If (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122) Or _
(letter >= 48 And letter <= 57) Then
Strip = Strip & Mid(strMessage, intCounter, 1)
Else: Strip = Strip
End If

Next

End Function

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-31 : 08:22:37

how about you tell us what this function does?

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-31 : 08:39:42

Why do you want to do this in sql?

Madhivanan

Failing to plan is Planning to fail

harrisw48
Starting Member

34 Posts

Posted - 2005-10-31 : 08:39:55

OK, this function removes any spaces and none alpha characters from a string

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-31 : 08:44:24

See if this helps you
http://weblogs.sqlteam.com/brettk/archive/2005/06/22/6328.aspx

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-31 : 08:46:28

look at this:
http://weblogs.sqlteam.com/brettk/archive/2005/06/22/6328.aspx

you just fix it to extract chars and not numbers

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-31 : 08:47:06

hahaha:

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-31 : 09:06:33

i really like this one:


/*********************************
Removes any characters from 
@myString that do not meet the 
provided criteria.
*********************************/
CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN
 
	While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

	Return @myString
END
Go

--Use like

Select 
	dbo.GetCharacters('0123abcd4567','0-9'), 
	dbo.GetCharacters('0123abcd4567','a-z')

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-31 : 09:14:22

Good stuff. Simple and perfect

Madhivanan

Failing to plan is Planning to fail

harrisw48
Starting Member

34 Posts

Posted - 2005-11-01 : 08:42:28

That code works a treat.

If I wanted it to cycle through a table and create a field with the variable with the data removed how would I do that?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-01 : 09:06:48

For a select...


Select 
	YourCol,
	Filtered = dbo.GetCharacters(YourCol,'a-z')
From YourTable

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources