query the number of spaces in the string

Question

Hello.

I have a table with a column of strings. I need to display the number of spaces in the string column in a new column. For example, 'jA kk qa', in a new column, display 2.

How do I do this? Thanks in advance.

Answer 1

Hi @15431565

Try this query.

create table test(col varchar(100));
insert into test values
('jA kk qa'),
('N  lta'),
('KK Dt'),
('U PF GH'),
('V bag  ha'),
('jkj sa cc as  sk');

select *,len(col) - len(replace(col,' ','')) as spacenums from test;

Output:

Best regards,

Percy Tang