Solved

Wildcard Search with charindex parameters in MSSQL

Posted on 2014-09-24
10
276 Views
Last Modified: 2014-09-25
/****** Object:  Table [dbo].[tbl_messages]    Script Date: 09/23/2014 16:41:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_messages](
      [autoid] [int] IDENTITY(1,1) NOT NULL,
      [message] [nvarchar](max) NULL,
      [sourceaddress] [nvarchar](50) NULL,
      [destinationaddress] [nvarchar](50) NULL,
      [datecaptured] [datetime] NULL,
      [ipaddress] [nvarchar](200) NULL,
      [querystringpassed] [nvarchar](max) NULL
) ON [PRIMARY]
GO


the format of the messages is

"TV [Space] Message"

They are send text messages to a system which is then logging the messages.

The 4 Families are

1. Utere Family

2. Odigie Family

3. Uzodinma Family

4. Unanka Family

Now, People will send messages like

"TV I vote Odigie Family"

or

"TV I think the Unanka guys are great, I vote Unanka!"


We have the following search to get the number of votes per family

select sum(case when charindex('Vote Utere',message) > 0  then 1 else 0 end) as UtereCnt,
          sum(case when charindex('Vote Odigie',message) > 0  then 1 else 0 end) as OdigieCnt,
          sum(case when charindex('Vote Uzodinma',message) > 0  then 1 else 0 end) as UzodinmaCnt,
          sum(case when charindex('Vote Unanka',message) > 0  then 1 else 0 end) as UnankaCnt
from tbl_messages

But it does not take into account spelling mistakes like "Vote Uninka"

Is it possible to have wildcard searches like "Vote Ut%" for "Vote Utere"? etc...
0
Comment
Question by:souldj
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40343315
Hi,

Why you want to go with Charindex where you can straightaway use like

select	sum(case when message like '%Vote Ut%' then 1 else 0 end) as UtereCnt,
			sum(case when message like '%Vote Od%' then 1 else 0 end) as OdigieCnt,
			sum(case when message like '%Vote Uz%' then 1 else 0 end) as UzodinmaCnt,
			sum(case when message like '%Vote Un%' then 1 else 0 end) as UnankaCnt
from tbl_messages 

Open in new window

0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40343340
What happens if you got a message like:
"TV I vote Otere"
0
 
LVL 1

Author Comment

by:souldj
ID: 40343390
I tried

select      sum(case when message like '%Vote Ut%' then 1 else 0 end) as UtereCnt,
                  sum(case when message like '%Vote Od%' then 1 else 0 end) as OdigieCnt,
                  sum(case when message like '%Vote Uz%' then 1 else 0 end) as UzodinmaCnt,
                  sum(case when message like '%Vote Un%' then 1 else 0 end) as UnankaCnt
from tbl_messages

but where I was gettgin some results without the wildcard, now I get no results.

The parameters state that is the name does not start with the first 2 characgters, it does not count. so Otere is not counted
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40343401
Then it's even more simple. Why compare to full family name if you only want first 2 characters?
So no need for wildcards. Here you go:
select sum(case when charindex('Vote Ut',message) > 0  then 1 else 0 end) as UtereCnt,
           sum(case when charindex('Vote Od',message) > 0  then 1 else 0 end) as OdigieCnt,
           sum(case when charindex('Vote Uz',message) > 0  then 1 else 0 end) as UzodinmaCnt,
           sum(case when charindex('Vote Un',message) > 0  then 1 else 0 end) as UnankaCnt
 from tbl_messages 

Open in new window

0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40343402
In those cases,

we have to go for fuzzy grouping which is available in SSIS transformation.

http://ssis-tutorial-online.blogspot.in/2013/04/fuzzy-grouping-transformation.html

Hope this would help
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40343409
try it using PATINDEX()

SELECT
      SUM(CASE WHEN PATINDEX('%Vote Ut%', message) > 0 THEN 1 ELSE 0 END) AS UtereCnt
    , SUM(CASE WHEN PATINDEX('%Vote Od%', message) > 0 THEN 1 ELSE 0 END) AS OdigieCnt
    , SUM(CASE WHEN PATINDEX('%Vote Uz%', message) > 0 THEN 1 ELSE 0 END) AS UzodinmaCnt
    , SUM(CASE WHEN PATINDEX('%Vote Un%', message) > 0 THEN 1 ELSE 0 END) AS UnankaCnt
FROM tbl_messages

Open in new window

0
 
LVL 1

Author Comment

by:souldj
ID: 40343482
Here is an upload of sample data to work with .

Please try your examples with this.
helpsql.sql
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40343487
Did you get any issues with our solutions?
0
 
LVL 1

Author Comment

by:souldj
ID: 40343491
ok, i just tried them again and they seem to work. :-) Must have been a spelling mistake or something...

Thanks will send points shortly.
0
 
LVL 1

Author Closing Comment

by:souldj
ID: 40343588
Quick and simple solutions. Thanks guys
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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