Searching for specific words - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Searching for specific words

Author

Topic

cocoa11
Starting Member

2 Posts

Posted - 2013-03-13 : 09:48:35

Below is an example of the field I am looking at. I am trying to count specific occurances of words. The field is how users are searching. More than one criteria can show up per field.

blahblahblah, Last Name: fakename
blahblahblah, ClientID: fakenumber
blahblahblah, Birth Date: fakedate
blahblahblah, First Name: fakename, Last Name: fakename

This works for me, but I do not want each criteria to be it's own column. I want then all in a column with a count of how many times it appears.

DECLARE @BirthDate Integer
DECLARE @SSN Integer
DECLARE @LastName Integer
DECLARE @FirstName Integer

SET @BirthDate = 0
SET @SSN = 0
SET @LastName = 0
SET @FirstName = 0

SELECT
SUM(CASE WHEN Searchlog.Criteria LIKE '%Birth Date%' THEN @Birthdate + 1 END) as [DOB],
SUM(CASE WHEN Searchlog.Criteria LIKE '%SSN%' THEN @SSN + 1 END) AS [SSN],
SUM(CASE WHEN Searchlog.Criteria LIKE '%Last Name%' THEN @LastName + 1 END) AS [LastName],
SUM(CASE WHEN Searchlog.Criteria LIKE '%First Name%' THEN @FirstName+ 1 END) AS [FirstName]

FROM Searchlog

DOB|SSN|LastName|FirstName|
2 |4 |17 |3 |

I feel like I might be over commplicating things.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 10:24:08

[code]
SELECT t.val,t1.cnt
FROM
(
SELECT 'Birth Date' AS val UNION ALL
SELECT 'SSN' UNION ALL
SELECT 'Last Name' UNION ALL
SELECT 'First Name'
)t
CROSS APPLY (SELECT COUNT(*) AS Cnt
FROM SearchLog
WHERE Criteria LIKE '%' + t.val + '%'
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cocoa11
Starting Member

2 Posts

Posted - 2013-03-13 : 10:45:58

Perfect! And thanks for the speedy reply!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:17:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources