Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.
You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))) ,0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0- 9]%',SUBSTRING(@.InputString,@.i+1,len(@.In
putString))
),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike