CODEWITHSUNDEEP
sqlsql-server
user5058225
user5058225

Reputation:

How to count a number of substring in a row via SQL?

I am working on a database represeting a simple address book through MS Studio 2015 (C#) and MS SQL Server 2008. I successfully added 'insert row' and 'remove row' methods in my code. So I want to compose a query (a stored procedure) which counts a number of substring in every row.

For example, I have the database which includes a table called Contacts:

PersonID  Name       Surname   City       Phone
1         Alice      Karlsson  Gotheburg  69-58-12
2         Mark       Morrow    Stockholm  48-48-48
3         Katherine  Karlsson  Gotheburg  69-58-16

If I try to find and count 'th' in the table, I want to get the following the result:

PersonID  Name       Surname   City       Phone     Count
3         Katherine  Karlsson  Gotheburg  69-58-16  2
1         Alice      Karlsson  Gotheburg  69-58-12  1

So I don't know how to do that. I've been googling for all the day but I didn't find the satisfying result. Here on the stackoverflow.com I find a solution returning the next result:

ColumnName     ColumnValue
Contacts.City  Gotheburg
Contacts.Name  Katherine
Contacts.City  Gotheburg

Please, give me any idea to compose a query returning the expected result.

Full-text search; is the expected result

UPD: 'th' is a substring I'm looking for in a row. So it should count "Agathe', 'th' and 'youth' the same way.

Upvotes: 0

Views: 1257

Answers (4)

Boody
Boody

Reputation: 144

You need to create a table valued function that loops on all rows column by column to seek the sub-string with a counter, inside the loop you can use built in functions that help in seeking texts such as CHARINDEX('th',Name+Surname+City,0)

which gives the exact location of the sub-string inside the text ...

Upvotes: 0

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

You should try following, 

Select 
    PersonId,
    Name,
    Surname,
    City,
    Phone,
    sum(count) as count
From
(
    select 
        *,
        (Len(name) - LEN(REPLACE(name, 'th', ' ')) + 
        Len(surname) - LEN(REPLACE(surname, 'th', ' ')) + 
        Len(city) - LEN(REPLACE(city, 'th', ' '))) as count

    from Contacts 
    where name like '%th%' or surname like '%th%' or city like '%th%'
)T
Group by PersonId, Name, Surname, City, Phone
Order by 6 desc

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this way

select * from Contacts where Contacts.City like '%th%' or 
Contacts.Name like '%th%'

Upvotes: 0

Sanu Antony
Sanu Antony

Reputation: 364

Here what you are trying to achieve is fulltext search... Please follow this link.. http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

create a full text index

and use this script 

select * from yourtable
where freetext (*,'your_search_item')

Upvotes: 0

Related Questions