Help with SQL Select with ^^^^

Question

Help with SQL Select with ^^^^

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

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

David Burrows SSC Guru Points: 65138 More actions · Answer 1

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: 1003851 More actions · Answer 2

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.

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

David Burrows SSC Guru Points: 65138 More actions · Answer 3

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.