Number of spaces between first and last Name in MS SQL

1

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
2

This is an awful way to hold customer names as you cannot really tell what is the first, middle and last name.

With regards searching for spaces, try something like:

SELECT C.ID, MAX(DATALENGTH(S.[value])) AS MaxSpaces
FROM
(
	VALUES (SPACE(1)), (SPACE(2)), (SPACE(3)), (SPACE(4))
		,(SPACE(5)), (SPACE(6)), (SPACE(7)), (SPACE(8))
) S ([value])
	JOIN [dbo].[CustomerData] C
		ON C.CustomerName LIKE '%' + S.[value] + '%'
GROUP BY C.ID;
1 Like
3

If you want to know the number of spaces so you can reduce them to 1 there is a trick:

DECLARE @Table TABLE
(
	Firstname NVARCHAR(250) NULL,
	LastName NVARCHAR(250) NULL
)

INSERT INTO @Table
SELECT '   TOM ', '   AND    JERRY'
UNION
SELECT 'TOM ', '   AND    JERRY'
UNION
SELECT '         TOM ', '           AND    JERRY'
UNION
SELECT '     TOM ', 'AND    JERRY'
UNION
SELECT '     TOM ', 'AND            JERRY'

SELECT * FROM @Table;


UPDATE @Table SET	Firstname=REPLACE(Firstname, ' ','{}'),
					Lastname=REPLACE(LastName, ' ','{}')

SELECT * FROM @Table;

UPDATE @Table SET	Firstname=REPLACE(Firstname, '}{',''),
					Lastname=REPLACE(LastName, '}{','')

SELECT * FROM @Table;

UPDATE @Table SET	Firstname=LTRIM(RTRIM(REPLACE(Firstname, '{}',' '))),
					Lastname=LTRIM(RTRIM(REPLACE(LastName, '{}',' ')))

SELECT * FROM @Table;
1 Like
4

I only have select permission on this table. This is a third party vendor database and I cannot update/delete/insert or create procedure/functions in this database. I am getting the data using select query