asked on

SQL LIKE Statement

Table1: (Contains employee pin number)

123456
456789
652136
235689
159753
etc.....

Table2: (Contains cust#(6), inv#(10),employee comments(max)

1111112222222222123456Hello Joe1123456Whats going on159753Nothing going on here
4444443333333333652136Hi235689Go Bears652136Go Cubs and Soxs456789No way
6666669999999999159753Superbowl here we come456789No way, New England will win

What I need to do, is count how many times a pin# appears in each record of table 2's employee comments field.
I need count by custno and inv#.
There are many comments stored in the employee comment field.
If the same pin# appears more than once in same record, then you count more then once.
I am thinking about using the LIKE statement somehow, but not sure how.

1111112222222222 should have a count of 3
4444443333333333 should have a count of 4
6666669999999999 should have a count of 2
etc........

Lee Wadwell

I tried this with the like ... but a LIKE is only going to match once and a pin can only join once ... needs more thought.

with example_list as (
select 123456 as pin union all
select 456789 as pin union all
select 652136 as pin union all
select 235689 as pin union all
select 159753 as pin
), example_strings as (
select '1111112222222222123456' as cust, 'Hello Joe1123456Whats going on159753Nothing going on here' as comments union all
select '4444443333333333652136' as cust, 'Hi235689Go Bears652136Go Cubs and Soxs456789No way235689' as comments union all
select '6666669999999999159753' as cust, 'Superbowl here we come456789No way, New England will win' as comments
)
select cust, count(*)
  from example_strings es
  join example_list el on es.comments like '%'+convert(varchar, el.pin)+'%'
 group by cust

Open in new window

Jim Horn

^^^ Nice

Lee Wadwell

This uses a recurring cte approach to look up multiple times. I suspect on large tables and lookup lists that performance will not be too good.

with example_list as (
select 123456 as pin union all
select 456789 as pin union all
select 652136 as pin union all
select 235689 as pin union all
select 159753 as pin
), example_strings as (
select '1111112222222222' as cust, '123456Hello Joe1123456Whats going on159753Nothing going on here' as comments union all
select '4444443333333333' as cust, '652136Hi235689Go Bears652136Go Cubs and Soxs456789No way' as comments union all
select '6666669999999999' as cust, '159753Superbowl here we come456789No way, New England will win' as comments
), search_string_recur as (
select cust, comments, el.pin,
       charindex(convert(varchar, el.pin),comments)+len(el.pin) as next_ind
  from example_strings es
  join example_list el on charindex(convert(varchar, el.pin),comments) > 0
union all
select cust, comments, el.pin,
       charindex(convert(varchar, el.pin),comments,next_ind)+len(el.pin) as next_ind
  from search_string_recur es
  join example_list el on charindex(convert(varchar, el.pin),comments,next_ind) > 0
 where next_ind < len(comments)
)
select cust, count(distinct next_ind)
  from search_string_recur
 group by cust

Open in new window

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thayduck

ASKER

Thanks.

Played around with your solution and it is doing exactly what I want it to do.

thayduck

ASKER

to lwadwell:

Would your last solution work if I was searching for dates.

For example, could I substitute pin 123456 for 08/16/2012 and your code would still work properly. I tried a couple of tests and it does not seem to work the same.

I also need to sum the number of different dates to. Sometimes dates are keyed in as 08/16/2012 or 2010-05-27 or 06/15/12.

Lee Wadwell

If the date was a varchar/char ... maybe. If it is a date/datetime datatype ... the convert(varchar, el.pin) needs to be changed to handle the format you want and it can only do one.
This version is a count per cust & pin:

with example_list as (
select 123456 as pin union all
select 456789 as pin union all
select 652136 as pin union all
select 235689 as pin union all
select 159753 as pin
), example_strings as (
select '1111112222222222' as cust, '123456Hello Joe1123456Whats going on159753Nothing going on here' as comments union all
select '4444443333333333' as cust, '652136Hi235689Go Bears652136Go Cubs and Soxs456789No way' as comments union all
select '6666669999999999' as cust, '159753Superbowl here we come456789No way, New England will win' as comments
)
select cust, pin, sum(occurs)
  from (select cust, pin, (len(es.comments)-len(replace(es.comments,convert(varchar, el.pin),'')))/len(el.pin) as occurs
          from example_strings es
          join example_list el on es.comments like '%'+convert(varchar, el.pin)+'%')v
 group by cust, pin

Open in new window

thayduck

ASKER

The mm/dd/yyyy is a char(10).
The yyyy-mm-dd is a char(10).
The mm/dd/yy is a char(8).

I was going to run your code doing 1 date format at a time.
So, I would run this process 3 separate times.

What is happening:

Need to count how many times a pin appears in a comment. This tells me that this long comment is actually 2 or 4 or 5 etc. separate comments buried in the long comment.

Next, I need to find how many dates are in this long comment. The dates are entered in the afore mentioned date formats.

thayduck

ASKER

I did below for dates and your codes works fine and comes up with correct date count.

select '08/16/2012' as pin union all
select '2010-03-30' as pin

Thanks again.