How to Find Multiple Occurrence of Particular String and Fetch Value in SQL Server

Get multiple occurrence of string from a column in sql query

Find a record which has multiple occurrences?

try this

SELECT Acode, COUNT(Bcode) 
FROM TableName
GROUP BY Acode
HAVING COUNT(Bcode) > 1

Getting the second occurrence from charindex function in sql server

You need to set START_LOCATION for CHARINDEX. It means after what character charindex should be found. In our example, we need to find after 56. So the code should looks like this:

select CHARINDEX(' ', 'Posted/edited: 56 days ago', 
    CHARINDEX('56', 'Posted/edited: 56 days ago', 0));

OUTPUT:

18

Get text after second occurrence of a specific character

Call the CHARINDEX function twice:

SELECT SUBSTRING(
           code,
           NULLIF(CHARINDEX('_', code, NULLIF(CHARINDEX('_', code), 0) + 1), 0) + 1,
           LEN(code)
       )
FROM (VALUES
    ('a_b_c'),
    ('a_b')
) x(code)

Finding multiple occurrence

In MySQL (as the question was originally tagged), you can use use ORDER BY and LIMIT to return one row:

SELECT CUST_ID, COUNT(CUST_ID) AS C
FROM BANK_FD_ACCOUNT
GROUP BY CUST_ID
ORDER BY C DESC
LIMIT 1;

In the event of duplicates, this will return an arbitrary customer. If you want all of them, use RANK():

SELECT c.*
FROM (SELECT CUST_ID, COUNT(*) AS C,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM BANK_FD_ACCOUNT
      GROUP BY CUST_ID
     ) c
WHERE seqnum = 1;

You can use ROW_NUMBER() to return one row (analogous to LIMIT in the MySQL example).

---

Related Topics