SQL Server How to get the 2nd substring in between delimiters with SQL? [duplicate]

gfttwv5a 于 2023-05-16 发布在其他

关注(0)|答案(1)|浏览(131)

This question already has answers here:

Using T-SQL, return nth delimited element from a string (14 answers)
Closed 3 days ago.

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.

sql-server

来源：https://stackoverflow.com/questions/76240130/how-to-get-the-2nd-substring-in-between-delimiters-with-sql

1条答案

按热度按时间

gjmwrych1#

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

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server How to get the 2nd substring in between delimiters with SQL? [duplicate]

1条答案

相关问题

热门标签

最新问答