number of spaces between first and last name

Question

I want to find the space between First Name and Last Name in SQL Server or First Name and Middle Name. I am aware of space function, but not sure if we can use it to find the space between two words. My table has around one million rows in the table. One of the columns is customerName. First Name, Middle Name and Last Name are stored as a combined name in the same row so for e.g.

Steven Ramirez is stored in the CustomerName column. Below is my table

 CREATE TABLE [dbo].[CustomerData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](500) NULL
) ON [PRIMARY]
GO
These are the names in the table:

INSERT INTO [dbo].[CustomerData]
           ([CustomerName])
     VALUES
           ('Steven    Ramirez')
GO

INSERT INTO [dbo].[CustomerData]
           ([CustomerName])
     VALUES
           ('Steven    Middle  Ramirez')

           go

           INSERT INTO [dbo].[CustomerData]
           ([CustomerName])
     VALUES
           ('Steven   Ramirez')
I want to find the space between First Name and Last Name and also First Name and Middle Name in SQL Server. So for all the rows, I should get

4
4
3
because there are 4 spaces between steven and Ramirez and there are 4 spaces between Steven and Middle and 3 spaces between steven and Ramirez

Steven    Ramirez  --4
Steven    Middle  Ramirez--4
Steven   Ramirez--3
So I just want to return

4
4
3
Any help will be appreciated.

Answer 1

Hi @Anjali Agarwal

You can try this.

;with T1 as(
  select CustomerName, 
     stuff(reverse(stuff(reverse(CustomerName),1,len(lastname) + 1, '')),1,len(firstname) + 1, '') as middle
  from (select CustomerName, 
      left(CustomerName, charindex(' ', CustomerName) - 1) as firstname, 
      right(CustomerName, charindex(' ', reverse(CustomerName)) - 1) as lastname 
     FROM [dbo].[CustomerData]) c
),T2 as (
  select CustomerName,trim(middle) as middle from T1)
select case when middle = '' then LEN(CustomerName)-LEN(REPLACE(CustomerName, ' ', '')) 
	   else charindex(middle,CustomerName) - charindex(' ',CustomerName) end as spacecount from T2;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I want to find the space between First Name and Last Name

Use LEN function and REPLACE to get the difference of length with and without spaces

declare @name nvarchar(100) = N'Steven    Ramirez'

SELECT LEN(@name) - LEN(REPLACE(@name, ' ', ''))

also First Name and Middle Name

How to differentiat between first, middle and last name (and a my second last name)? You have unstructured data.