Tsql kullanarak dizeden e-posta adresini çıkart

Mevcut bir yorum alanından e-posta adreslerini çıkarmaya ve kendi sütununa koymaya çalışıyorum. Dize bunun gibi bir şey olabilir "bu, [email protected] adresine ait bir e-posta adresi ile örnek bir yorumdur" ya da tam anlamıyla e-postanın kendisi "[email protected]" adresine gönderilecektir.

Yapılması gereken en iyi şey '@' sembolünün dizinini bulmak ve dizenin sonuna gelinceye kadar ya da bir boşluk olana kadar her iki yönde de arama yapmak olacaktır. Birisi bu uygulama ile bana yardımcı olabilir?

4
PATINDEX'i e-posta adresinin başlangıç ​​konumunu bulmak için kullanırdım. E-posta adreslerinin kalıplarını çevrimiçi olarak arayın: en basitinden en karmaşık kalıplara kadar,SQL-servertarafından bile tanınamayacak olanları bulabilirsiniz. Daha sonra bir sonraki boşluğu veya dizgenin sonunu bulmak için CHARINDEX kullanacağım (eğer CHARINDEX hiçbir şey döndürmezse)
katma yazar cha, kaynak

6 cevap

Wewesthemenace'in soruyu zaten cevapladığını biliyorum, ancak çözümü çok karmaşık görünüyor. Neden e-posta adresinin sol ve sağ taraflarını bir araya getirelim? E-posta adresinin başlangıcını ve sonunu bulmayı ve bunun gibi e-posta adresini döndürmek için alt dizini kullanmayı tercih ederim:

Benim masam

DECLARE @Table TABLE (comment NVARCHAR(50));
INSERT INTO @Table
VALUES ('blah [email protected]'),            --At the end
        ('blah [email protected] blah blah'), --In the middle
        ('[email protected] blah'),           --At the beginning
        ('no email');

Gerçek Sorgu:

SELECT  comment,        
        CASE
            WHEN CHARINDEX('@',comment) = 0 THEN NULL
            ELSE SUBSTRING(comment,beginningOfEmail,endOfEmail-beginningOfEmail)
        END email
FROM @Table
CROSS APPLY (SELECT CHARINDEX(' ',comment + ' ',CHARINDEX('@',comment))) AS A(endOfEmail)
CROSS APPLY (SELECT DATALENGTH(comment)/2 - CHARINDEX(' ',REVERSE(' ' + comment),CHARINDEX('@',REVERSE(' ' + comment))) + 2) AS B(beginningOfEmail)

Sonuçlar:

comment                                            email
-------------------------------------------------- --------------------------------------------------
blah [email protected]                     [email protected]
blah [email protected] blah blah           [email protected]
[email protected] blah                     [email protected]
no email                                           NULL
7
katma
LEN (), uçlardaki beyaz boşluğu yok sayar. DATALENGTH (), beyaz boşluğu yok sayar, ancak biraz farklı çalışır. Baytları listeler. Böylece VARCHAR (Unicode olmayan) byte = dizenin uzunluğu. NVARCHAR (Unicode) için, DATALENGTH() öğesinin 2'ye bölünmesi gerekir.
katma yazar Stephan, kaynak
Ne demek istediğimi göstermek için kodumu değiştirdim
katma yazar Stephan, kaynak
Benim için iyi çalıştı ve benim için yanlış sonuçlara neden olan kabul edilen çözümden çok daha az ayrıntılı. Ayrıca LEN'in bir dizenin sonunda beyaz boşluk içermediği gerçeğini öğrenmek için, bunu asla bilmiyordum!
katma yazar Shaun, kaynak
Bunu kullandığımda "sola veya alt dize işlevine iletilen geçersiz uzunluk parametresi" istisnası atıyor gibi görünüyor.
katma yazar Anthony, kaynak
Masamdaki veri türü nvarchar olduğundan DATALENGTH'i LEN olarak değiştirdim ve iyi oldum. Teşekkürler.
katma yazar Anthony, kaynak

Dizede '@' 'i arayabilirsiniz. Ardından dizgiyi '@' öğesinin LEFT ve RIGHT tarafında alın. Daha sonra REVERSE LEFT tarafını almak ve önce '' oluşumunu elde etmek ve ardından SUBSTRING 'ı oradan almak istersiniz. Ardından, orijinal formu almak için REVERSE 'i tıklayın. REVERSE yapmadan RIGHT tarafına da aynı prensip uygulanır.

Example string: 'some text [email protected] some text'

  1. LEFT = 'some text someemail'
  2. RIGHT = '@domain.org some text'
  3. Reverse LEFT = 'liameemos txet emos'
  4. SUBSTRING up to the first space = 'liameemos'
  5. REVERSE(4) = someemail
  6. SUBSTRING (2) up to the first space = '@domain.org'
  7. Combine 5 and 6 = '[email protected]'

Sorgunuz:

;WITH CteEmail(email) AS(
    SELECT '[email protected]' UNION ALL
    SELECT 'some text [email protected] some text' UNION ALL
    SELECT 'no email'
)
,CteStrings AS(
    SELECT
        [Left] = LEFT(email, CHARINDEX('@', email, 0) - 1),
        Reverse_Left = REVERSE(LEFT(email, CHARINDEX('@', email, 0) - 1)),
        [Right] = RIGHT(email, CHARINDEX('@', email, 0) + 1)
    FROM CteEmail
    WHERE email LIKE '%@%'
)
SELECT *,
    REVERSE(
        SUBSTRING(Reverse_Left, 0, 
            CASE
                WHEN CHARINDEX(' ', Reverse_Left, 0) = 0 THEN LEN(Reverse_Left) + 1
                ELSE CHARINDEX(' ', Reverse_Left, 0)
            END
        )
    )
    +
    SUBSTRING([Right], 0,
        CASE
            WHEN CHARINDEX(' ', [Right], 0) = 0 THEN LEN([Right]) + 1
            ELSE CHARINDEX(' ', [Right], 0)
        END
    )
FROM CteStrings

Örnek Veri:

email
----------------------------------------
[email protected]
some text [email protected] some text
no email

Sonuca

---------------------
[email protected]
[email protected]
5
katma
SUBSTRING , LEFT ve RIGHT işlevlerini okuduğunuzdan emin olun.
katma yazar Felix Pamittan, kaynak
Bu harika çalışıyor!
katma yazar Anthony, kaynak

Stephan's answer is great when looking for a single email address in each row.

Ancak, her satırda birden fazla e-posta adresi almaya çalışırken bu hatayı çalıştırıyordum:

Geçersiz uzunluk parametresi LEFT veya SUBSTRING işlevine geçirildi

@ içindeki tüm konumları almak için DBA Yığın Değişim Borsası'ndan gelen bu yanıtı kullandım. Dize Dize içindeki belirli bir kalıba eşit pozisyon sayısını döndüren tablo değerli bir fonksiyon gerektirir. Ayrıca, birden çok e-posta adresini de işlemek için CROSS APPLY işlevlerini değiştirmek zorunda kaldım.

My Table:

DECLARE @Table TABLE (comment VARCHAR(500));
INSERT INTO @Table (comment)
VALUES ('blah blah [email protected] more blah [email protected] even more blah [email protected]'),
       ('blah [email protected] more'),
       ('no email')

Table-valued Function:

CREATE FUNCTION dbo.fnFindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
        SELECT pos = Number - LEN(@term) 
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
        CHARINDEX(@term, @string + @term, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
        WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string))
        AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);
GO

Query:

SELECT comment, pos, SUBSTRING(comment,beginningOfEmail,endOfEmail-beginningOfEmail) AS email
FROM @Table
CROSS APPLY (SELECT pos FROM dbo.fnFindPatternLocation(comment, '@')) AS A(pos)
CROSS APPLY (SELECT CHARINDEX(' ',comment + ' ', pos)) AS B(endOfEmail)
CROSS APPLY (SELECT pos - CHARINDEX(' ', REVERSE(SUBSTRING(comment, 1, pos))) + 2) AS C(beginningOfEmail)

Results:

comment
---------------------------------------------------------------------------------------------------------
blah blah [email protected] more blah [email protected] even more blah [email protected]
blah blah [email protected] more blah [email protected] even more blah [email protected]
blah blah [email protected] more blah [email protected] even more blah [email protected]
blah [email protected] more

pos    email
---    ------------------------------
26     [email protected]
64     [email protected]
95     [email protected]
17     [email protected]
4
katma
Çok yardımcı teşekkür ederim.
katma yazar Barry Guvenkaya, kaynak
DECLARE @t TABLE (row_id INT, email VARCHAR(100))

INSERT @t (row_id, email)
VALUES (1, 'drgkls, [email protected], @ [email protected]'),
        (2, '[email protected] [email protected] [email protected] [email protected]@.dfj'),
        (3, '[email protected] лдоврывплдоо [email protected] [email protected],dd.com')

DECLARE @pat VARCHAR(100) = '%[^[email protected]_ ]%';

WITH f AS (
         SELECT    row_id,
                 CAST(' ' + email + ' ' AS VARCHAR(102)) email,
                 SUBSTRING(email, PATINDEX(@pat, email), 1) bad,
                 PATINDEX(@pat, email) pat
         FROM    @t
         UNION ALL
         SELECT    row_id,
                 CAST(REPLACE(email, bad, ' ') AS VARCHAR(102)),
                 SUBSTRING(REPLACE(email, bad, ' '), PATINDEX(@pat, REPLACE(email, bad, ' ')), 1) bad,
                 PATINDEX(@pat, REPLACE(email, bad, ' '))
         FROM    f
         WHERE    PATINDEX(@pat, email) > 0
     ),
     s AS 
     (
         SELECT    row_id,
                 email, PATINDEX('%@%', email) pos 
         FROM    f 
         WHERE    pat = 0
                 AND    PATINDEX('%@%', email) > 0
         UNION ALL
         SELECT    row_id,
                 SUBSTRING(email, pos + 1, 102), 
                 PATINDEX('%@%', SUBSTRING(email, pos + 1, 102))
         FROM    s
         WHERE    PATINDEX('%@%', SUBSTRING(email, pos + 1, 102)) > 0
     )

SELECT  row_id, o1 + pp
FROM    s   
        CROSS APPLY (SELECT    REVERSE(LEFT(email, pos -1)) s1) x
        CROSS APPLY (SELECT    CHARINDEX(' ', s1) i1) y
        CROSS APPLY (SELECT    REVERSE(LEFT(s1, i1 -1)) o1 WHERE i1 > 0) z
        CROSS APPLY (SELECT    CHARINDEX(' ', email, pos) i2) e
        CROSS APPLY (SELECT    SUBSTRING(email, pos, i2 -pos) pp WHERE    i2 > pos + 1) q
WHERE    LEN(o1) > 1
        AND CHARINDEX('.', pp) > 0
        AND PATINDEX('%@%@%', pp) = 0
        AND PATINDEX('%@.%', pp) = 0
        AND PATINDEX('%.', pp) = 0
2
katma

Yeni satır karakterleri içeren karakter dizileri için Felix'in cevabını, boşluktan ziyade ilk kontrol karakterini aramak için PATINDEX kullanarak değiştirdim.

Ayrıca, doğru miktarda metni çıkarmak için Right alanını değiştirmek zorunda kaldım.

    WITH CteEmail(email) AS(
        SELECT 'example string with new lines

    Email: [email protected]
(first email address - should be returned)

    Email: [email protected]
(other email addresses should be ignored

more example text' UNION ALL
        SELECT 'Email: [email protected]' UNION ALL
        SELECT '[email protected]' UNION ALL
        SELECT 'some text [email protected] some text' UNION ALL
        SELECT 'no email'
    )
    ,CteStrings AS(
        SELECT
            [Left] = LEFT(email, CHARINDEX('@', email, 0) - 1),
            Reverse_Left = REVERSE(LEFT(email, CHARINDEX('@', email, 0) - 1)),
            [Right] = RIGHT(email, LEN(email) - CHARINDEX('@', email, 0) + 1 )
        FROM CteEmail
        WHERE email LIKE '%@%'
    )
    SELECT *,
        REVERSE(
            SUBSTRING(Reverse_Left, 0, 
                CASE
                    WHEN PATINDEX('%[' + CHAR(10)+'- ]%', Reverse_Left) = 0 THEN LEN(Reverse_Left) + 1
                    ELSE PATINDEX('%[' + CHAR(0)+'- ]%', Reverse_Left)
                END
            )
        )
        +
        SUBSTRING([Right], 0,
            CASE
                WHEN PATINDEX('%[' + CHAR(0)+'- ]%', [Right]) = 0 THEN LEN([Right]) + 1
                ELSE PATINDEX('%[' + CHAR(0)+'- ]%', [Right])
            END
        )
    FROM CteStrings
0
katma

Bu bir çizgi de işe yarar (lol olsa da biraz uzun bir çizgi):

--declare @a varchar(100) 
--set @a = 'a asfd saasd [email protected] wqe z zx cxzc '
select substring(substring(@a,0,charindex('@',@a)),len(substring(@a,0,charindex('@',@a)))-charindex(' ',reverse(substring(@a,0,charindex('@',@a))))+2,len(substring(@a,0,charindex('@',@a)))) + substring(substring(@a,charindex('@',@a),len(@a)),0,charindex(' ',substring(@a,charindex('@',@a),len(@a))))
0
katma