SQL query to find substring between repeating characters

Question

I need a SQL query to get the substring between the second and third occurrence of the same character within a field. In this case the repeating character is a hat ^. As an example,

Party_Name      Address
IBM             IBM ^ IBM USA BR 1002 ^ 680 Langly Drive CA ^ NE ^ 0232232^ CC^

The ^ symbol appears multiple times in the address field, how do I retrieve "680 Langly Drive CA" only?

I have this so far

select Party_Name,
       substring(Address, charindex('^', Address....)
from table_name

I am not sure what to do next

Please update the tags of the question with the RDBMS you are using using (microsoft sql server, mysql, postgres, oracle, etc) — JNevill, Sep 26 '22 at 14:42
Does this answer your question? [Equivalent of explode() to work with strings in MySQL](https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql) — tobiv, Sep 26 '22 at 14:44

score 0 · Answer 1 · answered Sep 26 '22 at 15:44

Considering you wanted to extract the string between 2nd and 3rd symbol only.

SELECT Party_Name ,SUBSTRING([Address2],1,CHARINDEX('^',[Address2])-1)
FROM 
    (
      SELECT Party_Name, SUBSTRING([Address1],charindex('^', 
             [Address1])+1,LEN([Address1])-CHARINDEX('^',[Address1] )) AS [Address2]
      FROM (    
                SELECT
                Party_Name,
                SUBSTRING([Address],CHARINDEX('^',[Address])+1,LEN([Address])-charindex('^',[Address] )) AS [Address1]
                FROM Table_Name
            )Ad1
    )Ad2

Pavlo Kuchereshko · Answer 2 · 2022-09-26T15:08:20.710

-1

try something like substring in substring, as the guy did here

edited Sep 26 '22 at 15:08

answered Sep 26 '22 at 14:47

Pavlo Kuchereshko

74
8

SQL query to find substring between repeating characters

2 Answers2