query the number of spaces in the string

Potter123 260 Reputation points
2023-08-24T08:42:18.5033333+00:00

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.

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
    2023-08-24T08:47:49.5366667+00:00

    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:

    User's image

    Best regards,

    Percy Tang

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful