Selecting data from a pipe delimited column

Question

I have a column ClientInformation in a Table CurrentOrders that contains a pipe delimited string:

5275=7147|109=62F36|5075=BCRM|12221=PortwareALGO1

each number= is a FIX tag that I want to name as its own column and need to be able to use in a SELECT statement and WHERE clause.

I hacked something that let me identify rows i needed, but I can't use in SELECT or WHERE...

   (LEFT (RIGHT(ClientInformation, ( LEN(ClientInformation) - (PATINDEX('%109=%',ClientInformation) + 3) )),
    PATINDEX('%|%', RIGHT(ClientInformation, ( LEN(ClientInformation) - (PATINDEX('%109=%',ClientInformation) + 2) ))) -2))as AccountID

I am assuming i need to break each number= out into its own temp column in my table, but I am having difficulty finding an explanation I can follow.

So, each of these values I would like as follows:

5275=7147|109=62F36|5075=BCRM|12221=PortwareALGO1

5275= as ClientBook
109= as AccountID
5075= as Broker ID
12221= as RouteTrace

ignore, I see now it's for SQL Server (patindex) – Paul Maxwell Jun 22 '14 at 05:49 — Paul Maxwell, Jun 22 '14 at 05:49

Paul Maxwell · Answer 1 · 2014-06-22T06:24:24.127

The following approach uses multiple CROSS APPLYs because the alias given to each calculation is reusable in the next CROSS APPLY and overall this helps simplify the code (IMHO).

SELECT
      ClientBook
    , AccountID
    , BrokerID
    , RouteTrace
FROM CurrentOrders
      CROSS APPLY (
            SELECT
                  PATINDEX('%|%', ClientInformation)
                , LEN(ClientInformation)
      ) ca1 (p1, len1)
      CROSS APPLY (
            SELECT
                  p1 + PATINDEX('%|%', SUBSTRING(ClientInformation, p1 + 1, len1))
      ) ca2 (p2)
      CROSS APPLY (
            SELECT
                  p2 + PATINDEX('%|%', SUBSTRING(ClientInformation, p2 + 1, len1))
      ) ca3 (p3)
      CROSS APPLY (
            SELECT
                  SUBSTRING(ClientInformation, 1, p1 - 1)
                , SUBSTRING(ClientInformation, p1 + 1, p2 - p1 - 1)
                , SUBSTRING(ClientInformation, p2 + 1, p3 - p2 - 1)
                , SUBSTRING(ClientInformation, p3 + 1, len1)
      ) ca4 (s1, s2, s3, s4)
      CROSS APPLY (
            SELECT
                  LEFT(s1, PATINDEX('%=%', s1) - 1)
                , LEFT(s2, PATINDEX('%=%', s2) - 1)
                , LEFT(s3, PATINDEX('%=%', s3) - 1)
                , LEFT(s4, PATINDEX('%=%', s4) - 1)
      ) ca5 (ClientBook, AccountID, BrokerID, RouteTrace)
;
| CLIENTBOOK | ACCOUNTID | BROKERID | ROUTETRACE |
|------------|-----------|----------|------------|
|       5275 |       109 |     5075 |      12221 |

see: http://sqlfiddle.com/#!3/f85bc/3

Selecting data from a pipe delimited column

1 Answers1