-1

I'm trying to query data from a column that has 3 values separated by " | " as a delimiter. I'm having trouble trying to extract the second value without creating an additional column

Example of data:

{cookie_data}

732711 | 732711@soundcorp.com | 732711 Store

I want to get the email portion only.

SELECT 
       SUBSTRING(cookie_data, CHARINDEX(' | ', cookie_data)  + 3, LEN(cookie_data)) AS login_data,
       SUBSTRING(login_data, 0, CHARINDEX(' | ', login_data)) AS email,
FROM TABLE

While this solution works, I was curious if there was an easier way of doing this without generating an extra column that isn't needed other than to create the final substring.

jp207
  • 94
  • 7
  • always a good read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk May 12 '23 at 22:02
  • Take a peek at https://stackoverflow.com/questions/74227722/split-t-sql-string-into-columns-by-delimiter/74227778#74227778 – John Cappelletti May 13 '23 at 12:18

1 Answers1

1

you can use string_split instead of substring but I wrote with two ways(substring without New Column,string_split )

select *
from string_split('732711 | 732711@soundcorp.com | 732711 Store','|')
value
732711
732711@soundcorp.com
732711 Store

1.Get Email(with PAttern):

select  *
from string_split('732711 | 732711@soundcorp.com | 732711 Store','|')
WHERE value  LIKE '%_@__%.__%'

2.Get Email(with order):

select value from (
        select  *,ROW_NUMBER() over(order by (select 0)) as rw
        from string_split('732711 | 732711@soundcorp.com | 732711 Store','|')

)a
where a.rw=2

3.Get Email(with Substring):

declare @m nvarchar(100)='732711 | 732711@soundcorp.com | 732711 Store'
select   SUBSTRING(@m, charindex('|', @m)+1, len(@m) - CHARINDEX('|', reverse(@m)) - charindex('|', @m))

Result:

value
732711@soundcorp.com
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20