Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rickincanada
Starting Member
18 Posts |
 Posted - 2012-08-22 : 16:25:00
|
| I have a bizarre table which is used to contain notes. Each customer has a single row within this table and then any notes associated with that customer are stored in a VARCHAR(8000) column called notes.Here is an example of what one of notes columns might contain:"Jim 01/01/2012 13:23 Customer Not Happy Jim 12/15/2011 09:15 Customer called AGAIN to see about tires Jim 12/15/2011 07:33 Customer called about tires"So here's what I'm trying to do: I want to count the number of times that a name and a specific date are inside this string. Using the example name of Jim and the date of 12/15/2011 (for a string of "Jim 12/15/2011") I should get back 2. Is this even possible?? |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2012-08-22 : 17:53:07
|
Try this:DECLARE @str VARCHAR(8000);DECLARE @len INT;SET @str = 'Jim 12/15/2011';SET @len = LEN(@str);IF @len = 0 SET @len = 1;SELECT (LEN(T.notes) - LEN(REPLACE(T.notes, @str, ''))) / @len FROM YourTable AS T For us, there is only the trying. The rest is not our business. ~T.S. EliotMuhammad Al Pasha |
 |
|
|
rickincanada
Starting Member
18 Posts |
Posted - 2012-08-24 : 00:00:39
|
| Brilliant! Works like a charm. |
|
|
|
|
|
|
Count Expressions in String