In string 0 to 9 number replace with blank in SQL

Question

I have String Like

208Pb,
75As,
111Cd

I want to relapse number with blank and result AS Like

Pb,
AS,
cd

@Vignesh Kumar Thnx a lot your answer is correct its work then why you delete your answer — Hiral Nayak, Jun 21 '14 at 09:11

score -1 · Accepted Answer · edited May 23 '17 at 12:27

-1

Source

Method 1:

Try this function:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('208Pb')

Output:

Pb

Method 2:

    DECLARE @str VARCHAR(400)
    DECLARE @expres  VARCHAR(50) = '%[0-9]%'
    SET @str = '208Pb'
    WHILE PATINDEX( @expres, @str ) > 0
      SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')

    SELECT @str

Output:

Pb

edited May 23 '17 at 12:27

Community

1
1

answered Jun 21 '14 at 08:51

Vignesh Kumar A

27,863
13
63
115

You have enough rep to vote to close as dupe rather than copy another answer. – Martin Smith Jun 21 '14 at 08:53
3

It's up to you if you want to delete it. But IMO you firstly should have considered crediting [the original author](http://stackoverflow.com/a/1008566/73226) of the `RemoveNonAlphaCharacters` function. Then you should have considered whether the fact that the answer was already found on SO probably makes the question a dupe. – Martin Smith Jun 21 '14 at 08:58

In string 0 to 9 number replace with blank in SQL

1 Answers1

Linked