Remove alpha chars & all spaces in field [UPDATED]

Hello,

I need to strip out all alpha chars and spaces in a given field and return only the numbers.

I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).

Any help would be appreciated.

UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?

Hi,

If it is possible to call the SQLServer function Please write the following function:

create FUNCTION [dbo].[GetNumberFromData]

(@.data VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @.pos BIGINT,@.char CHAR(1),@.rightside VARCHAR(MAX)

/*Set @.data = rtrim(ltrim(@.data))

While CharIndex(' ',@.data)>0

Select @.data = Replace(@.data, ' ', '')*/

SET @.pos = 1

WHILE (@.data like '%[^0-9]%') AND @.pos <= LEN(@.data)

BEGIN

SET @.char = SUBSTRING(@.data, @.pos, 1)

IF @.char like '%[0-9]%'SET @.pos = @.pos + 1

ELSE

BEGIN

SELECT @.rightside = SUBSTRING(@.data, @.pos + 1, LEN(@.data)),@.data = CASE WHEN @.rightside like '%[0-9]%' THEN

LEFT(@.data, @.pos - 1) + @.rightside

ELSE

LEFT(@.data, @.pos - 1)END

END

END

IF @.data like '%[^0-9]%' OR LEN(@.data) = 0

SET @.data = NULL

RETURN LTRIM(RTRIM(@.data))

End

and call the function like this:

If you can't able to call the function from report put the below statement in the stored procedure.and call that SP in your report.

select [dbo].[GetNumberFromData](' 1 800 , dd 798 hjjhjj kal 8976 asdf ')

Hope this helps.

Thanks

|||

Hello,

Here's a custom function I wrote that will strip all the non-numeric characters from a string. Go to Report Properties from the report menu, then in the Code tab, enter this:

Public Function GetNumbers(s as string) as Integer

Dim str as String

Dim i as Integer

str = ""

i = 0

While i < Len(s)

If (Asc(Mid(s, i + 1, 1)) >= 48 and Asc(Mid(s, i + 1, 1)) <= 57)

str = str + Mid(s, i + 1, 1)

End If

i = i + 1

End While

return cInt(str)

End Function

Then, in your textbox for the numbers, use this as your expression:

=Code.GetNumbers(Fields@.!Info.Value)

As for your Update... yes, you can use them both at the same time. =Right(Replace(Fields!Info.Value, " ", ""), 10)

Hope this helps.

Jarret

|||Thanks a BUNCH!