Find spaces between First and Last Name in Microsoft SQL

Question

Find spaces between First and Last Name in Microsoft SQL

Anjali66

SSC Enthusiast

Points: 173
More actions
June 23, 2023 at 5:18 am

#4217295

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

I'd use CHARINDEX. You can search for an expression, as simple as looking for the space, within the string and return the value where that space is.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

--======================================================================================================
--      Create and populate the test table. This is not a part of the solution.
--======================================================================================================
--===== Create the test table.
   DROP TABLE IF EXISTS #CustomerData;
 CREATE TABLE  #CustomerData
        (
         ID           int          IDENTITY(1,1) NOT NULL
        ,CustomerName varchar(500) NULL
        ) 
;
--===== Populate the test table with spaced names.
 INSERT INTO #CustomerData
        (CustomerName)
 VALUES  ('Steven    Ramirez')        --4 Spaces
        ,('Steven    Middle Ramirez') --4 Spaces from first to middle
        ,('Steven   Ramirez')         --3 Spaces
        ,('Steven Ramirez')           --1 Space
        ,('StevenRamirez')            --No Spaces
;
--======================================================================================================
--      Demonstrate a solution to how many spaces are in the first space block.
--======================================================================================================
 SELECT  cd.ID
        ,SpaceCount = PATINDEX('%[ ][^ ]%',fs.FirstSpaceStr)
   FROM #CustomerData cd
  CROSS APPLY (VALUES(SUBSTRING(cd.CustomerName,CHARINDEX(' ',cd.CustomerName),500)))fs(FirstSpaceStr)
  ORDER BY cd.ID
;

Results:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anjali66 SSC Enthusiast Points: 173 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 4

Anjali66 wrote:

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

Read the comments. The create table and insert are just to build the test data. The final select is all you need. Just change the table name from a temp table to your actual table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 5

@anjali66 ,

Just curious... did it work for you after my clarification above?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 6

Jeff Moden wrote:

@Anjali66 ,
Just curious... did it work for you after my clarification above?

No answer... Ok then... good luck! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kajaldigital8700@gmail.com SSC Rookie Points: 43 More actions · Answer 7

In Microsoft SQL, you can find the spaces between the first and last name using various string manipulation functions. One approach is to use the CHARINDEX function, which returns the starting position of a specified substring within a string. By searching for the space character (' ') in the column containing the full name, you can identify the position of the space. For example, the query "SELECT CHARINDEX(' ', FullName) AS SpacePosition FROM TableName" will retrieve the position of the first space in the FullName column. This allows you to extract the first name and last name by utilizing string functions such as LEFT and RIGHT. Remember to adjust the query according to your table and column names.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 8

kajaldigital8700@gmail.com wrote:

In Microsoft SQL, you can find the spaces between the first and last name using various string manipulation functions. One approach is to use the CHARINDEX function, which returns the starting position of a specified substring within a string. By searching for the space character (' ') in the column containing the full name, you can identify the position of the space. For example, the query "SELECT CHARINDEX(' ', FullName) AS SpacePosition FROM TableName" will retrieve the position of the first space in the FullName column. This allows you to extract the first name and last name by utilizing string functions such as LEFT and RIGHT. Remember to adjust the query according to your table and column names.

The OP provided test data. Please code your example and provide it. No fair copying mine, either. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Emperor100 SSCrazy Points: 2173 More actions · Answer 9

Here is another way.

DROP TABLE IF EXISTS #CustomerData;

CREATE TABLE #CustomerData(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](500) NULL
) 
GO

INSERT INTO #CustomerData ([CustomerName]) VALUES ('Steven    Ramirez');
INSERT INTO #CustomerData ([CustomerName]) VALUES ('Steven    Middle Ramirez');
INSERT INTO #CustomerData ([CustomerName]) VALUES ('Steven   Ramirez');

WITH cte (CustName, CountSpaces)
AS
(SELECT
Customername
,PATINDEX('%[ ][a-zA-Z]%', c.CustomerName) - PATINDEX('%[a-zA-z][ ]%', c.CustomerName) CountSpaces
FROM #CustomerData c)
SELECT
CustName, CountSpaces
FROM cte t

=======================================================================

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 10

Nice.... you don't even need the CTE with that method.

SELECT
Customername
,PATINDEX('%[ ][a-zA-Z]%', c.CustomerName) - PATINDEX('%[a-zA-z][ ]%', c.CustomerName) CountSpaces
FROM #CustomerData c

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)