query the number of spaces in the string

Potter123 260 Reputation points


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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,584 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

    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;


    User's image

    Best regards,

    Percy Tang

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful