Help with SQL Select with ^^^^

  • David Burrows

    SSC Guru

    Points: 64733

    WHERE SUBSTRING([column],CHARINDEX('^',[column],PATINDEX('%PQ5|1|[0-9]%',[column])),4) = '^^^^'

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 996661

    David Burrows wrote:

    WHERE SUBSTRING([column],CHARINDEX('^',[column],PATINDEX('%PQ5|1|[0-9]%',[column])),4) = '^^^^'

    If you look at the following post where the OP finally posted some semblance of the data he's actually trying to filter, you'll see why such a nice KISS method doesn't work.  I deleted my post that demonstrated a method that does work according to the spec on that post because of one of the responses from the OP.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • David Burrows

    SSC Guru

    Points: 64733

    Jeff Moden wrote:

    David Burrows wrote:

    WHERE SUBSTRING([column],CHARINDEX('^',[column],PATINDEX('%PQ5|1|[0-9]%',[column])),4) = '^^^^'

    If you look at the following post where the OP finally posted some semblance of the data he's actually trying to filter, you'll see why such a nice KISS method doesn't work.  I deleted my post that demonstrated a method that does work according to the spec on that post because of one of the responses from the OP.

    Unfortunately I did not see your solution, so I posted what I thought was a simple solution that returned rows 1-4 of the 5 samples rows posted. Why do you say it wont work? I understand that the data contains more than 4 carets and the OP stated 4 but they also stated to return the first 4 rows.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply