Solved

Wildcard Search with charindex parameters in MSSQL

Posted on 2014-09-24
10
273 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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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 47

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

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

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

832 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