Solved

Wildcard Search with charindex parameters in MSSQL

Posted on 2014-09-24
10
262 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 14

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 45

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
 
LVL 45

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 14

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 45

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
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 to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now