How to remove non-Numeric or non-alphameric characters from a string in Sql Server 2005

Hi to all,

I am having a string like (234) 522-4342.

i have to remove the non numeric characters from the above string.

Please help me in this regards.

Thanks in advance.

M.ArulMani

Hi,

How about this:

Public Function StripAlpha(ByVal PassedStr)As String' ' Leaves only any numeric values in passed string 'Dim iAs Integer Dim GotCharAs Boolean Dim HoldStrAs String =""Dim HoldCharAs String =""'Clears Leading / trailing spaces PassedStr = Trim(PassedStr)' Cycle through the string to remove unwanted charactersFor i = 1To Len(PassedStr) HoldChar = Mid$(PassedStr, i, 1)If Not IsNumeric(HoldChar)Then GotChar =True Else GotChar =False End If If Not GotCharThen HoldStr = HoldStr & HoldCharEnd If Next i StripAlpha = HoldStrEnd Function

You just pass in something like StripAlpha("xyz()12345") and it should return just the numbers.

Hope this helps.

Paul

|||

Duplicate post. Please use the other thread if anyone would like to contribute:http://forums.asp.net/t/1111146.aspx

How to remove non-Numeric or non-alphameric characters from a string in Sql Server 2005

Hi to all,

I am having a string like (234) 522-4342.

i have to remove the non numeric characters from the above string.

Please help me in this regards.

Thanks in advance.

M.ArulMani

Hi,

How about this:

Public Function StripAlpha(ByVal PassedStr)As String' ' Leaves only any numeric values in passed string 'Dim iAs Integer Dim GotCharAs Boolean Dim HoldStrAs String =""Dim HoldCharAs String =""'Clears Leading / trailing spaces PassedStr = Trim(PassedStr)' Cycle through the string to remove unwanted charactersFor i = 1To Len(PassedStr) HoldChar = Mid$(PassedStr, i, 1)If Not IsNumeric(HoldChar)Then GotChar =True Else GotChar =False End If If Not GotCharThen HoldStr = HoldStr & HoldCharEnd If Next i StripAlpha = HoldStrEnd Function

You just pass in something like StripAlpha("xyz()12345") and it should return just the numbers.

Hope this helps.

Paul

|||

Well I dont recommend looping through the string its better if you use Regular Expressions.

I have bloged about this here :http://dotnetolympians.wordpress.com/2007/01/25/trimming-a-string/

Regular expressions are faster and use lesser code all this above can be done in 1-2 lines of code :).

How to Query to get the Numeric fields Only in the Table

hi Good Day everyone.

how can i query numeric values in the specific fields for example EmployeeID only.

but the field EmployeeID has a datatype of varchar.

for example :

EmployeeID -> Field

001

002

a

b

c

how do i query 001 and 002 only. tnx

here it is,

Code Snippet

Create Table #employee (

[EmployeeID] Varchar(100)

);

Insert Into #employee Values('001');

Insert Into #employee Values('002');

Insert Into #employee Values('a');

Insert Into #employee Values('b');

Insert Into #employee Values('c');

Insert Into #employee Values('1E2');

Insert Into #employee Values('.');

Insert Into #employee Values('$');

Insert Into #employee Values('E0');

Select

*

from

#employee

Where

Isnumeric([EmployeeID]) = 1

And [EmployeeID]not like '%[^0-9]%'

|||Thanks Manivannan.D.Sekaran it helps me a lot.