SQL query to find substring between repeating characters-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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