Search code examples
sql-serversubstringcharindex

Filter a specific word in SQL Server by row number

I have a table below:

| Row | RecordLocator | Comment    
|-----|---------------|------------------------------------------------------------------    
|  1  |     AAA111    | SearchCrt:ONEWAY   -   EMAIL:0:[email protected]   -   EMAIL:1:[email protected]   -   PassangerContact: xxxyy  - Promo:0257 - ......
|  2  |     AAA111    | SearchCrt:ONEWAY   -   EMAIL:0:[email protected]   -   EMAIL:1:[email protected]   -   PassangerContact: xxxyy  - Promo:0257 - ......
|  1  |     BBB111    | PassangerContact: jrte   -  PersonID:12  -  EMAIL:0:[email protected]   -   BagTag: 12315 - .......
|  1  |     CCC111    | Promo:5474   -  EMAIL:0:[email protected]   -   BagTag: 12315 -  EMAIL:1:[email protected] -  EMAIL:2:[email protected] - .......
|  2  |     CCC111    | Promo:5474   -  EMAIL:0:[email protected]   -   BagTag: 12315 -  EMAIL:1:[email protected] -  EMAIL:2:[email protected] - .......
|  3  |     CCC111    | Promo:5474   -  EMAIL:0:[email protected]   -   BagTag: 12315 -  EMAIL:1:[email protected] -  EMAIL:2:[email protected] - .......
|  1  |     DDD111    | Promo:89474   - BagTag: 147515 - dds2121sdsd1a2 -  221221gdfgf - .......

And I need the result below:

| Row | RecordLocator | E-mail    
|-----|---------------|------------------------------------------------------------------    
|  1  |     AAA111    | [email protected]
|  2  |     AAA111    | [email protected]
|  1  |     BBB111    | [email protected]
|  1  |     CCC111    | [email protected]
|  2  |     CCC111    | [email protected]
|  3  |     CCC111    | [email protected]
|  1  |     DDD111    | not found

The EMAIL:0: necessary to add the line 1, EMAIL:1: necessary to add the line 2 .... But only the e-mail. If e-mail not be found show a message not found.

I tried the query below:

SELECT DISTINCT 
            tmpRow.Row
            , tmpRow.RecordLocator
            , isNull(searchEmail.Email, 'not found')
         FROM #TmpRow tmpRow

         CROSS APPLY
         (

            SELECT SUBSTRING ( tmpRow2.Comment , (CHARINDEX('EMAIL:'+ (tmpRow2.Row - 1) +':', tmpRow2.Comment)) , 20 ) AS Email 
                FROM #TmpRow tmpRow2
            WHERE tmpRow.Row = tmpRow2.Row
              AND tmpRow.RecordLocator = tmpRow2.RecordLocator

         )searchEmail

But no success, I need stop in - char and the CHARINDEX not working too.

I'm using SQL Server 2008.

Solution

  • Here is another way to do it along with sample data in a cte.

    with something(RowNum, RecordLocator, Comment) as
(
    select 1, 'AAA111', 'SearchCrt:ONEWAY - EMAIL:0:[email protected] - EMAIL:1:[email protected] - PassangerContact: xxxyy - Promo:0257 - ......' union all
    select 2, 'AAA111', 'SearchCrt:ONEWAY - EMAIL:0:[email protected] - EMAIL:1:[email protected] - PassangerContact: xxxyy - Promo:0257 - ......' union all
    select 1, 'BBB111', 'PassangerContact: jrte - PersonID:12 - EMAIL:0:[email protected] - BagTag: 12315 - .......' union all
    select 1, 'CCC111', 'Promo:5474 - EMAIL:0:[email protected] - BagTag: 12315 - EMAIL:1:[email protected] - EMAIL:2:[email protected] - .......' union all
    select 2, 'CCC111', 'Promo:5474 - EMAIL:0:[email protected] - BagTag: 12315 - EMAIL:1:[email protected] - EMAIL:2:[email protected] - .......' union all
    select 3, 'CCC111', 'Promo:5474 - EMAIL:0:[email protected] - BagTag: 12315 - EMAIL:1:[email protected] - EMAIL:2:[email protected] - .......' union all
    select 1, 'DDD111', 'Promo:89474 - BagTag: 147515 - dds2121sdsd1a2 - 221221gdfgf - .......'
)

select RowNum
    , RecordLocator
    , case when CHARINDEX('EMAIL:' + cast(RowNum - 1 as char(1)), Comment) > 0 
        then SUBSTRING(Comment, CHARINDEX('EMAIL:' + cast(RowNum - 1 as char(1)), Comment) + 8, CHARINDEX(' ', Comment, CHARINDEX('EMAIL:' + cast(RowNum - 1 as char(1)), Comment)) - CHARINDEX('EMAIL:' + cast(RowNum - 1 as char(1)), Comment) - 8)
        else 'not found' 
    end as Email
from something