Converting Access VBA code to sql

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

General SQL Server Forums

New to SQL Server Programming

Converting Access VBA code to sql

Author

Topic

madamvegas
Starting Member

3 Posts

Posted - 2012-05-10 : 10:23:44

I have an Access db that an employee created that had some VBA code in it that I need to convert to SQL. Not familiar with VBA and new to SQL. What the code does is replaces ASCII charcters with spaces.

Here is a snippet of the code.

Function Dead(TextIN As String, Optional NonPrints As Boolean) As String
Dim Str As String

Str = Trim(TextIN)

If NonPrints Then
Dim x As Long
' remove all non-printable characters
While InStr(Str, vbCrLf) > 0
Str = Replace(Str, vbCrLf, " ")
Wend

For x = 126 To 160
While InStr(Str, Chr(x)) > 0
Str = Replace(Str, Chr(x), "")
Wend
Next x

End If

While InStr(Str, String(2, " ")) > 0
Str = Replace(Str, String(2, " "), " ")
Wend
Dead = Str

End Function

So not sure how to look for ASCII characters in SQL and how to do the loop.

Any help to get me in the right direction would be appreciated.

Theresa Burk

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-05-10 : 11:26:10

Assuming you want to learn to fish vs. have the fish handed to you:

I suggest you
- browse this site's Script Library forum.
- get used to referring to Books Online
ie: built in string functions

you can start with this:


select ascii('t'), char(116)

declare @i int
       ,@s varchar(50)
       
select @i = 126
       ,@s = 'madam' + char(128) + 'vegas'

select @s

while @i < 161
begin
       set @s = replace(@s, char(@i), ' ')
       set @i = @i+1
end

select @s

OUTPUT:
            
----------- ----
116         t


--------------------------------------------------
madam€vegas



--------------------------------------------------
madam vegas

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources