return separate character from a string

Question

How to return all the characters from a string and count it in sql.

if the string is "how are you"

it should return

char count

Why have SQL do this? SQL is not a programming language it is a data storage and data retrieve software. What you describe would be easily solved by a coding langauge — Noam Rathaus, Jan 08 '14 at 10:38
What if you need the query that returns all rows containing the highest occurences of the letter 'o'? Seems a bit complicated but it can still be interesting. — Pierre Arlaud, Jan 08 '14 at 10:40
He is asking, break down string to its components, seek this component throughout my original string return the numbers. This isn't SQL this is madness :} — Noam Rathaus, Jan 08 '14 at 10:41
it will return those chars which are in the string in an ascending oreder — SaNa3819, Jan 08 '14 at 10:42
@nrathaus For performance issues you are right, but the question can try to solve a maintenance issue instead. — cubitouch, Jan 08 '14 at 10:43
have a look at following post http://stackoverflow.com/a/9789266/1292203 — Usman Kurd, Jan 08 '14 at 10:43

peter.petrov · Accepted Answer · 2014-01-08T10:55:12.383

You can use this script. It will give you exactly what you need. This one counts just the letters in the string.

declare @c int
declare @ch varchar(10) 
declare @str varchar(max)
set @str = 'how are you'
declare @letter int 

declare @i int 
set @i = 1
create table #tbl(ch varchar(10), cnt int)

while (@i <= len(@str))
begin
   set @letter = 0
   set @ch = substring(@str, @i, 1)

   select @c = count(*) from #tbl 
   where ch = @ch 

   if ( (@ch >= 'a' and @ch <= 'z') or (@ch >= 'A' and @ch <= 'Z') )
   begin
      set @letter = 1
   end 

   if (@c = 0)
   begin
       if (@letter = 1)
       begin
           insert into #tbl (ch, cnt) values (@ch, 1)
       end
   end
   else
   begin
       update #tbl set cnt = cnt + 1 where ch = @ch
   end

   set @i = @i + 1

end


select * from #tbl 
drop table #tbl

And if you want to count all chars (not just letters), this makes it even easier. Use this script.

declare @c int
declare @ch varchar(10) 
declare @str varchar(max)
set @str = 'how are you'

declare @i int 
set @i = 1
create table #tbl(ch varchar(10), cnt int)

while (@i <= len(@str))
begin
   set @ch = substring(@str, @i, 1)

   select @c = count(*) from #tbl 
   where ch = @ch 

   if (@c = 0)
   begin
       insert into #tbl (ch, cnt) values (@ch, 1)
   end
   else
   begin
       update #tbl set cnt = cnt + 1 where ch = @ch
   end

   set @i = @i + 1

end


select * from #tbl 
drop table #tbl

can u tell me select @c = count(*) from #tbl where ch = @ch before this line only the table is created so how it is compared ch with @ch? — SaNa3819, Jan 08 '14 at 11:26
The ch is the column name in #tbl. The @ch is just a single variable, it is a different name from ch. So these are two different names of two different things in this script. — peter.petrov, Jan 08 '14 at 11:27
Am trying to say that initially the table is empty.select @c = count(*) from #tbl where ch = @ch so how can we get value from this? — SaNa3819, Jan 08 '14 at 11:32
Right but I then check if @c is 0, if so, I insert a row in #tbl. And if @c is not zero, I update the existing row (for that particular char we're on). — peter.petrov, Jan 08 '14 at 11:33

score 0 · Answer 2 · answered Jan 08 '14 at 10:42

0

You can use a customer tsql function, see http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-Split-a-308206f3.

And you can make a query solve your issue using group by and count statements ?

answered Jan 08 '14 at 10:42

cubitouch

1,929
15
28

score 0 · Answer 3 · answered Jan 08 '14 at 11:06

This will return the result set you have requested. It does this by taking each letter and adding it to a new row within a temporary table and then querying the results to return the counts for each occurrence of the character.

DECLARE @individual CHAR(1);

DECLARE @text NVARCHAR(200)
SET @text = 'how are you';

IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
    DROP TABLE #tmpTable

CREATE TABLE #tmpTable (letter char(1));

WHILE LEN(@text) > 0
BEGIN       
    SET @individual = SUBSTRING(@text, 1, 2)
    INSERT INTO #tmpTable (letter) VALUES (@individual);

    SET @text = SUBSTRING(@text, 2, LEN(@text))
END

SELECT letter, COUNT(*) AS [count]
FROM #tmpTable
GROUP BY letter;

return separate character from a string

3 Answers3