CODEWITHSUNDEEP
sqlstringcount
tony barretto
tony barretto

Reputation: 43

how to count each characters in a string and display each instance in a table format

my table master_schedule

cable_no
D110772
D110773
D110774
D110775
D110776
D110777
D110778
D110779
D110880

I would like to create a loop so that each character in the string counted and displayed 

D 9
1 18
2 1
3 1
AND SO ON .......
how can i modify in these sql query mentioned below: 
select (sum(LEN(cable_no) - LEN(REPLACE(cable_no, 'D', '')))*2) as FERRUL_qtyx2
from MASTER_schedule

Upvotes: 1

Views: 149

Answers (2)

roman
roman

Reputation: 117370

you can use recursive common table expression:

with cte(symbol, cable_no) as (
    select
        left(cable_no, 1), right(cable_no, len(cable_no) - 1)
    from Table1

    union all

    select
        left(cable_no, 1), right(cable_no, len(cable_no) - 1)
    from cte
    where cable_no <> ''
)
select symbol, count(*)
from cte
group by symbol

=> sql fiddle demo

Another approach (made after Gordon Linoff solution):

;with cte(n) as (
    select 1
    union all
    select n + 1 from cte where n < 7
)
select substring(t.cable_no, n.n, 1) as letter, count(*) as cnt
from #Table1 as t
    cross join cte as n
group by substring(t.cable_no, n.n, 1);

=> sql fiddle demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Something like this:

select substring(cable_no, n.n, 1) as letter, count(*) as cnt
from FERRUL_qtyx2 t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7
     ) n
group by substring(cable_no, n.n, 1);

This creates a sequence of numbers n up to the length of the string. It then uses cross join and substring() to extract the nth character of each cable_no.

In general, this will be faster than doing a union all seven times. The union all approach will typically scan the table 7 times. This will scan the table only once.

Upvotes: 1

Related Questions