[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL LIKE Statement

Posted on 2012-08-17
9
Medium Priority
?
408 Views
Last Modified: 2012-08-19
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........
0
Comment
Question by:thayduck
  • 4
  • 4
9 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38304920
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

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38304969
^^^ Nice
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38305079
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

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 25

Accepted Solution

by:
lwadwell earned 800 total points
ID: 38305171
This is much better ... it uses a REPLACE, then detect the change in LEN() on the string to determine the number of occurrences.
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, sum(occurs)
  from (select cust, (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

Open in new window

0
 

Author Closing Comment

by:thayduck
ID: 38305616
Thanks.

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

Author Comment

by:thayduck
ID: 38306343
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.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38306869
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

0
 

Author Comment

by:thayduck
ID: 38308541
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.
0
 

Author Comment

by:thayduck
ID: 38309776
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.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question