Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Wildcard Search with charindex parameters in MSSQL

/****** 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
souldj
Asked:
souldj
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Vikas GargBusiness Intelligence DeveloperCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What happens if you got a message like:
"TV I vote Otere"
0
 
souldjAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vikas GargBusiness Intelligence DeveloperCommented:
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
 
PortletPaulCommented:
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
 
souldjAuthor Commented:
Here is an upload of sample data to work with .

Please try your examples with this.
helpsql.sql
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you get any issues with our solutions?
0
 
souldjAuthor Commented:
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
 
souldjAuthor Commented:
Quick and simple solutions. Thanks guys
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now