replace unwanted characters 2

[AtomicWedgie]

Hi,

is there an easy way to replace characters like:

_ - ~ ! @ # $ % ^ & * ( ) : ; " ' { } | [ ] | < > ?

in one go without writing a replace for each character?

Regards,

Atomic Wedgie

 

[DotNetGnat]

yes...use REGEX() function....

as i can see you need to eliminte all the nonalphanumeric characters...

and the regex pattern is [^a-zA-Z0-9_]

just read more on regular expressions...

BTW this is at the script level...i am not sure how you do at the database level...

-DNG

 

[AtomicWedgie]

Hmm,

I want to solve this using a SQL statement.

Regards,

Atomic Wedgie

 

[DotNetGnat]

ok then...look for PATINDEX in books online

-DNG

 

[Jamfool]

Could write it to extract what data u do want:

CREATE FUNCTION dbo.fnMakeAlphaNumeric(@value varchar(255))
RETURNS varchar(255)
AS
BEGIN
WHILE PATINDEX('%[^a-z0-9]%', @value) > 0
BEGIN
SET @value = STUFF(@value, PATINDEX('%[^a-z0-9]%', @value), 1, '')
END

RETURN @value
END
GO

print dbo.fnMakeAlphaNumeric
('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ!"£$%^&*()_+=-[]{};#,./{}~<>?')

drop function dbo.fnMakeAlphaNumeric

 

[DotNetGnat]

so i was right in my suggestions to use Patindex and [^a-z0-9], just did not know how to put them in a function...

Jamfool, thanks for providing the functions...seems very useful to me...

-DNG

 

[AtomicWedgie]

Hi jamfool,

maybe now I am asking a bit to much but could you also write this in VB code? Then I can use it in a dts package.

Regards,

Atomic Wedgie

 

[DotNetGnat]

this function strips the non-alphanumeric characters..

Public Function OnlyAlphaNumericChars(ByVal OrigString As _
  String) As String

    Dim lLen As Long
    Dim sAns As String
    Dim lCtr As Long
    Dim sChar As String
    
    OrigString = Trim(OrigString)
    lLen = Len(OrigString)
    For lCtr = 1 To lLen
        sChar = Mid(OrigString, lCtr, 1)
        If IsAlphaNumeric(Mid(OrigString, lCtr, 1)) Then
            sAns = sAns & sChar
        End If
    DoEvents '(optional, but if processing long string,
    'necessary to prevent program from appearing to hang)
    'if used, write your app so no re-entrancy into this function
    'can occur)
    Next
        
    OnlyAlphaNumericChars = sAns

End Function

Private Function IsAlphaNumeric(sChr As String) As Boolean
    IsAlphaNumeric = sChr Like "[0-9A-Za-z]"
End Function

-DNG

 

[AtomicWedgie]

Hi,

as it turns out the code should be in VBscript. I have tried putting the code in but it doesn't seem to recognise the IsAlfaNumeric expression.

Any ideas?

Regards,

Atomic Wedgie

 

[DotNetGnat]

Just remove the Public and Private keywords before the functions...

-DNG