May 10, 2012 at 8:29 pm
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.
May 11, 2012 at 2:03 am
tburk 5368 (5/10/2012)
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.
this is fairly easy in SQL, but to replace as many characters as you are in this function it might be very time consuming
there are 3 functions to acheive this examples below)
CHAR(137) - gets the ASCII character 137
ASCII('a') gets the ASCII number for "a"
REPLACE('bbbaaaccc','a','x') - replaces all instances of "a" in the string with the character "x"
so you could combine these and do
REPLACE ('bbaabb',CHAR(1),' ')
you can line up the replace statements, but it soon gets ugly
REPLACE(REPLACE ('bbaabb',char(1),' '),'b','x')
imagine this with 40 replace statements
of you can do a loop
declare @STR varchar(1000)
declare @i int=137
while @i<=160
begin
set @STR=replace(@str,char(@i),' ')
set @i=@i+1
end
hope this helps
MVDBA
May 11, 2012 at 2:08 am
if forgot to add - there are certain limitations/ workarounds for the replace function with regards to length of the string being no more than 8000 characters (see books on line)
to be honest replace doesn't scale very well when the string is long - so i have in the past written a CLR in C++ unmanaged code which was a lot faster than calling the replace function in a loop
MVDBA
May 14, 2012 at 12:27 pm
I tried the loop and it cleared out the whole field and not just the ascii characters. i wrote it into a function
CREATE FUNCTION [dbo].[ReplaceCharacters] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
declare @STR varchar(1000)
declare @i int=93
DECLARE @OutputString VARCHAR(255)
while @i<=95
begin
set @STR=replace(@str,char(@i),' ')
set @i=@i+1
end
RETURN @OutputString
end
select dbo.replacecharacters(attvalue) as attlabel
from attribute_data
where (catalognum = 'D3SFKWB00A000XX')
When I ran the select statement I expected to see the Ascii characters for 93 to 95 to be replaced by a space. But it nulled out the whole field. Did I read the code wrong.
May 14, 2012 at 12:38 pm
tburk 5368 (5/14/2012)
I tried the loop and it cleared out the whole field and not just the ascii characters. i wrote it into a functionCREATE FUNCTION [dbo].[ReplaceCharacters] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
declare @STR varchar(1000)
declare @i int=93
DECLARE @OutputString VARCHAR(255)
while @i<=95
begin
set @STR=replace(@str,char(@i),' ')
set @i=@i+1
end
RETURN @OutputString
end
select dbo.replacecharacters(attvalue) as attlabel
from attribute_data
where (catalognum = 'D3SFKWB00A000XX')
When I ran the select statement I expected to see the Ascii characters for 93 to 95 to be replaced by a space. But it nulled out the whole field. Did I read the code wrong.
You declare @OutputString but never set it to anything. Why not just remove that and return @STR instead?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2012 at 12:44 pm
tburk 5368 (5/14/2012)
I tried the loop and it cleared out the whole field and not just the ascii characters. i wrote it into a functionCREATE FUNCTION [dbo].[ReplaceCharacters] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
declare @STR varchar(1000)
declare @i int=93
DECLARE @OutputString VARCHAR(255)
while @i<=95
begin
set @STR=replace(@str,char(@i),' ')
set @i=@i+1
end
RETURN @OutputString
end
select dbo.replacecharacters(attvalue) as attlabel
from attribute_data
where (catalognum = 'D3SFKWB00A000XX')
When I ran the select statement I expected to see the Ascii characters for 93 to 95 to be replaced by a space. But it nulled out the whole field. Did I read the code wrong.
Actually looking a bit closer, this function has a lot of problems. You have data size mismatches all over the place. You have extra variables and you never evaluate the @InputString.
There are certainly better set based alternatives to this but something like this would make your function work correctly.
CREATE FUNCTION [dbo].[ReplaceCharacters]
(
@InputString varchar(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
declare @STR varchar(4000)
set @STR = @InputString
declare @i int = 93
while @i <= 95
begin
set @STR=replace(@str,char(@i),' ')
set @i=@i+1
end
RETURN @STR
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2012 at 5:59 pm
Thanks I'll give it a try. Out of office for a few days. I'll let you know how it goes
May 15, 2012 at 1:16 am
lookking at it now
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply