Remove digits from string which is ending by digits

Question

I have a column of Emails. In that column there are email addresses which end with some digits. I want to remove those trailing digits.

Example:

jjj@yahoo.com234
lhf@hotmail.com1984
lll@gmail.com567

Result:

jjj@yahoo.com
lhf@hotmail.com
lll@gmail.com

Thank you very much.

https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server — apomene, Sep 26 '17 at 08:32
The result which I mention in my question is what I'm expecting. — bizimunda, Sep 26 '17 at 08:55
just use the query update dbo.Email set Email = REPLACE(Email, SUBSTRING(Email, PATINDEX('%[1-9]%', Email), LEN(Email)), '') — Yogesh Sharma, Sep 26 '17 at 09:07
with your this query it removes all digits wherever he founds in the field. — bizimunda, Sep 26 '17 at 09:11
Possible duplicate of [Remove numbers from string sql server](https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server) — Tanner, Sep 26 '17 at 09:13
@user2983359 yes it will remove digits wherever it founds, & your question is to be like that. — Yogesh Sharma, Sep 26 '17 at 09:16
@user2983359 pls. check my updated answer, hope it will help you. — Yogesh Sharma, Sep 26 '17 at 09:51
@user2983359 have you checked the Manoj Pilania answer before accepting the solution? — Yogesh Sharma, Sep 26 '17 at 12:57
because of it removes all numbers from your email address not just ending digits. — Yogesh Sharma, Sep 26 '17 at 12:58
yes I have checked and apparently it does the same thing. Your script leave empty fields and his script put NULL in the empty fields — bizimunda, Sep 26 '17 at 14:31

Yogesh Sharma · Answer 1 · 2017-09-26T10:00:59.117

0

Check below :

DECLARE @DATA VARCHAR(32)

SET @DATA = 'jjj1@yahoo.com234'

SELECT REPLACE(@DATA, SUBSTRING(SUBSTRING(@DATA, CHARINDEX('@', @DATA) + 1, LEN(@DATA)), PATINDEX('%[0-9]%', SUBSTRING(@DATA, CHARINDEX('@', @DATA) + 1, LEN(@DATA))), LEN(SUBSTRING(@DATA, CHARINDEX('@', @DATA) + 1, LEN(@DATA)))), '') AS DATA

+================+
| DATA           |
+================+
| jjj1@yahoo.com |
+----------------+

Update query :

UPDATE dbo.Email
  SET
      Email = REPLACE(Email, SUBSTRING(SUBSTRING(Email, CHARINDEX('@', Email)+1, LEN(Email)), PATINDEX('%[0-9]%', SUBSTRING(Email, CHARINDEX('@', Email)+1, LEN(Email))), LEN(SUBSTRING(Email, CHARINDEX('@', Email)+1, LEN(Email)))), '');

edited Sep 26 '17 at 10:00

answered Sep 26 '17 at 08:32

Yogesh Sharma

49,870
5
26
52

your query just shows me the result but does not update my table. – bizimunda Sep 26 '17 at 08:57
show your actual query from which you would like to update the email. – Yogesh Sharma Sep 26 '17 at 09:03
If an email address contains numeric chars, like "myadress44@gmail.com" this script not working. – Serkan Arslan Sep 26 '17 at 09:08
i have given the answer according, the question. – Yogesh Sharma Sep 26 '17 at 09:08
yes you updated answer is working :). Thank you very much @Yogesh Sharma – bizimunda Sep 26 '17 at 10:03
@YogeshSharma If the domain name has any number like yahoo11.com your script doesn't work but I guess this is not necessary for the answer. – Serkan Arslan Sep 26 '17 at 10:44
So, now wht is the issue with current updated answer ?? – Yogesh Sharma Sep 26 '17 at 10:50

Manoj Pilania · Accepted Answer · 2017-09-26T11:06:53.047

0

You can try this solution, I used this many times.

Create a function from given code.

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE FUNCTION [dbo].[RemoveSpecialChars] (@s VARCHAR(2000)) RETURNS VARCHAR(2000)
     WITH SCHEMABINDING
  BEGIN
     IF @s IS NULL
        RETURN NULL
     DECLARE @s2 VARCHAR(256)
     SET @s2 = ''
     DECLARE @l INT
     SET @l = LEN(@s)
     DECLARE @p int
     SET @p = 1
     WHILE @p <= @l BEGIN
        DECLARE @c INT
        SET @c = ASCII(SUBSTRING(@s, @p, 1))
        IF @c BETWEEN 65 AND 90 OR @c BETWEEN 97 AND 122 OR @c = 64 OR @c = 46
           SET @s2 = @s2 + CHAR(@c)
        SET @p = @p + 1
        END
     IF LEN(@s2) = 0
        RETURN NULL
     RETURN @s2
  END

Then you can use this function given below query

    UPDATE [dbo].[Email]
    SET Email = AA.Email    
    FROM [dbo].[Email] AEL
    CROSS APPLY
    (SELECT TOP 1 [dbo].[RemoveSpecialChars](Email)AS Email FROM [dbo].[Email] at WHERE at.Email=AEL.Email) AA

edited Sep 26 '17 at 11:06

answered Sep 26 '17 at 09:29

Manoj Pilania

664
1
7
18

When I run your function I get this error message Invalid object name 'dbo.RemoveSpecialChars'. – bizimunda Sep 26 '17 at 09:59
yes your script is also working. But my question now is.. what is the difference between your scripts and between of @Yogesh Sharma? – bizimunda Sep 26 '17 at 11:12
I just write the function to remove numbers, this function can be reused. Yogesh is using string function, I am using ASCII code to check that the character is number or not – Manoj Pilania Sep 26 '17 at 12:14
have you checked the solution which you have given ? – Yogesh Sharma Sep 26 '17 at 13:00
1

it removes all numeric values rather than ending digits of email addresses. – Yogesh Sharma Sep 26 '17 at 13:01

Remove digits from string which is ending by digits

2 Answers2