How do I select a string within a string?

[suefhmis]

Any SQL gurus out there? I need a statement that will select a string from within a string. For example: the contents of note.note_01 for one owner looks like this:

Line one
Line two
**line three
^line four
~This is the string I need~
Line six

The string I need is surrounded by two tilde characters (~) and appears in different positions depending upon the owner to whom the note is attached (in other words, sometimes it's line three, four, or perhaps ten). What is the easiest way for me to grab just the string within the tildes?
Thanks!

 

[jawan]

Try Patindex() and charindex(), see help for these

 

[SQLBill]

Have you tried:

SELECT SUBSTRING(note.note_01,(PATINDEX('~',note.note_01)), ((PATINDEX('~', REVERSE(note.note_01)))-(PATINDEX('~',note.note_01))))
FROM mytable
WHERE note.note_01 LIKE '%~%~%'

I THINK that might work (test it and you might have to 'play' with it a bit).

It SHOULD grab the position of the first tilde, then grab the position of the last tilde and use those numbers to grab the substring beginning at the first tilde and ending with the last one. (SHOULD).

-SQLBill

 

[mwolf00]

SELECT subString(myCol, charindex('~',myCol)+1, charIndex('~',myCol, charindex('~',myCol)+1) - charindex('~',myCol)+1) from myTable

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[jhall01]

something alon the line of

Substring(note_01, patindex("%~%", note_01)+1 , patindex("%~%", substring(note_01, patindex("%~%", note_01)+ 1, len(patindex("%~%", note_01)+ 1))

There has got to be an easier way, but the problem is the string between the "~" is variable in length.

Substring(string - in this case note_01 begininnig,...
..., patindex("~", note_01) finds the first "~" and outputs it position and adds one to the start position after the "~".

the last part then looks for the patindex of the substring after the first "~" and looks for the "~" in that substring.

Kind of confusing....and most likely there is a better way but again, since your string is variable in length it makes it a bit more challenging

 

[jhall01]

i posted the same time as mwolf!!! HAHA

His is a very similar and either way works...i believe

 

[mwolf00]

Of course it all crashes if someone used a '~' in any of the notes prior to the intended note...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[suefhmis]

I've tried each of the statements you wonderful folks sent. The first one gave me an error about PATINDEX operating only on certain data types. So I tried the second one, as follows:

SELECT substring (note.note_01, charindex('~', note.note_01)+1,
charindex('~', note.note_01,charindex('~', note.note_01)+1)-
charindex('~', note.note_01)+1)
from note


And got the following results:
(5 row(s) affected)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Any further suggestions? I think the second statement will work, just can't get it thru my fuzzy brain how to send the length parameter.

 

[mwolf00]

Hmm - are there times when only one '~' is present? That could be the problem...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[suefhmis]

That's possible. Although I was assured that each record that needed one was edited to contain an ending ~...

 

[mwolf00]

SELECT substring (note.note_01, charindex('~', note.note_01)+1,
charindex('~', note.note_01,charindex('~', note.note_01)+1)-
charindex('~', note.note_01)+1)
from note
WHERE note.note_01 LIKE '%~%~%'

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[clarkin]

I think it bears mentioning that this sort of string-searching can be really slow in SQL server - especially LIKE '%...

perhaps your business logic language can do it faster? (eg, your vbscript in ASP or VB in your windows program)

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[suefhmis]

mwolf, this worked beautifully. Thanks so much (everyone) for your help.

 

[mrfritz44]

Funny how we run into similar problems at the same time! Here's a stumper for all:
I have to extract data from a "double delimited string". For example, the string may look like this:
John Doe~Father~100~P, Jane Doe~Mother~101~P, Jerry Doe~Brother~102~S

This string can be of variable length but the minimum will be at least one complete set of the first 4 substrings (the ones separated by the first set of tildas within the first comma)

What I have to do is create a view that extracts the name and relationship of each family member in the string. It would look like this for example:
John Doe, Father/Jane Doe, Mother/Jerry Doe, Brother

As a secondary function, the view would store the number code in a separate cell.

Any suggestions?

Thank you, Fritz

 

[mwolf00]

I would tackle this in ASP - load the recordset and then load the field into an array based on the comma position (do any of the names contain commas - Doe, John ?) Then the task would be simple.

For a pure SQL solution, I would have to write a stored procedure or a VERY complex query...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mrfritz44]

Yes I agree with you on the ASP thing. We've already done it using VBScript and it works very well. Unfortunately, we're required to massage this data in a "View" format for Crystal Reports. Did I mention that I hate Crystal Reports? I spent yesterday trying to write the SQL query you mentioned, but I wasn't able to define the second tilda delimited substring. I'm at a loss.

I'll be working on it again today and checking back here on occasion if you come up with anything. Thanks for the reply.

Fritz

 

[mwolf00]

Will each person/family member get their own row in the view?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

pseudocode for a max of five members - may need adjusting

SELECT
case firstComma
when none then
left(str, firstTild - 1) + ',' + substring(str, firstTild + 1, secondTild - firstTild)
else
case secondComma
when none then ----------------two members
left(str, firstTild - 1) + ',' + substring(str, firstTild + 1, secondTild - firstTild-1) + '/' + substring(str, firstComma + 1, charIndex('~',str, firstComma) - firstComma-1) + ',' + substring(str, charIndex('~',str, firstComma)+1, charIndex('~',str,charIndex('~',str, firstComma)+1)
else
case thirdComma
when none then -------three members
left(str, firstTild - 1) + ',' + substring(str, firstTild + 1, secondTild - firstTild-1) + '/' + substring(str, firstComma + 1, charIndex('~',str, firstComma) - firstComma-1) + ',' + substring(str, charIndex('~',str, firstComma)+1, charIndex('~',str,charIndex('~',str, firstComma)+1) + '/' + substring(str, secondComma + 1, charIndex('~',str, secondComma) - secondComma-1) + ',' + substring(str, charIndex('~',str, secondComma)+1, charIndex('~',str,charIndex('~',str, secondComma)+1)
else
case fourthComma
when none then ----four members
left(str, firstTild - 1) + ',' + substring(str, firstTild + 1, secondTild - firstTild-1) + '/' + substring(str, firstComma + 1, charIndex('~',str, firstComma) - firstComma-1) + ',' + substring(str, charIndex('~',str, firstComma)+1, charIndex('~',str,charIndex('~',str, firstComma)+1) + '/' + substring(str, secondComma + 1, charIndex('~',str, secondComma) - secondComma-1) + ',' + substring(str, charIndex('~',str, secondComma)+1, charIndex('~',str,charIndex('~',str, secondComma)+1) + '/' + substring(str, thirdComma + 1, charIndex('~',str, thirdComma) - thirdComma-1) + ',' + substring(str, charIndex('~',str, thirdComma)+1, charIndex('~',str,charIndex('~',str, thirdComma)+1)
else
left(str, firstTild - 1) + ',' + substring(str, firstTild + 1, secondTild - firstTild-1) + '/' + substring(str, firstComma + 1, charIndex('~',str, firstComma) - firstComma-1) + ',' + substring(str, charIndex('~',str, firstComma)+1, charIndex('~',str,charIndex('~',str, firstComma)+1) + '/' + substring(str, secondComma + 1, charIndex('~',str, secondComma) - secondComma-1) + ',' + substring(str, charIndex('~',str, secondComma)+1, charIndex('~',str,charIndex('~',str, secondComma)+1) + '/' + substring(str, thirdComma + 1, charIndex('~',str, thirdComma) - thirdComma-1) + ',' + substring(str, charIndex('~',str, thirdComma)+1, charIndex('~',str,charIndex('~',str, thirdComma)+1) + '/' + substring(str, fourthComma + 1, charIndex('~',str, fourthComma) - fourthComma-1) + ',' + substring(str, charIndex('~',str, fourthComma)+1, charIndex('~',str,charIndex('~',str, fourthComma)+1)
end
end
end
end
FROM myTable

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mrfritz44]

Thank you so much for your time with this. You're right, that was a bear of a query. I'm going to spend the day trying to get it to work with our nomenclature.

Thanks!............and have a good weekend.

Fritz