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

Question

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.

Actually you should think up something different with fuzzy search support. If `like` fulfills your needs - build a column which is a combination of all values you need to search through. — Ivan Starostin, Feb 15 '16 at 11:49

Emipro Technologies Pvt. Ltd. · Answer 1 · 2016-02-15T10:38:23.130

1

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

edited Feb 15 '16 at 10:38

answered Feb 15 '16 at 10:13

Emipro Technologies Pvt. Ltd.

14,598
5
45
75

If I have a name like 'Agathe-Katherine' it must count twice. But your algorythm count the name only one time. How to make it count the real number of 'th' in the field? – Feb 15 '16 at 10:21
See I have replace core logic to find character occurrences in a string. – Emipro Technologies Pvt. Ltd. Feb 15 '16 at 10:39

score 0 · Answer 2 · answered Feb 15 '16 at 09:59

0

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')

answered Feb 15 '16 at 09:59

Sanu Antony

364
4
15

I did that way. I created a fulltext catalog and index, after that I used the script above. So as a result of searching 'th' or '%th%' it returns nothing. When I search for a normal name 'Alice' it returns me a row containing this field. What did I wrong? – Feb 15 '16 at 10:17

score 0 · Answer 3 · answered Feb 15 '16 at 10:14

0

Try this way

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

answered Feb 15 '16 at 10:14

Mukesh Kalgude

4,814
2
17
32

score 0 · Answer 4 · answered Feb 15 '16 at 10:39

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 ...

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

4 Answers4