?
Solved

Wildcard Search with charindex parameters in MSSQL

Posted on 2014-09-24
10
Medium Priority
?
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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 49

Accepted Solution

by:
PortletPaul earned 1000 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 51

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

770 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