CODEWITHSUNDEEP
sqlsql-server
M005
M005

Reputation: 854

Split middle initial from first name in sql server

I am looking to split middle name from first name, only if first name has space and after space also it should be 1 character, if it has more than 1 character we should consider whole string as firstname.

For example: input string is "MATE K" then it should be split into two part,

Firstname = MATE & MiddleInitial = K

BUT if input string is "MATE KATE" then it should not split and keep it as it is.

Firstname = MATE KATE

Here is example which I did work, but it does not give me output as expected.

Can someone please help me?

declare @name as varchar(50)
set @name ='MATE KATE'
select left(@name, CHARINDEX(' ', @name)) as FirstName,
substring(@name, CHARINDEX(' ', @name) +1, len(@name)-(CHARINDEX(' ',@name)-1)) as MiddleInitial

Upvotes: 2

Views: 2468

Answers (5)

gofr1
gofr1

Reputation: 15977

With the help of XML:

DECLARE @name varchar(50),
        @xml xml

SET @name ='Mate Kate'

SELECT @xml = CAST('<f><n>' + REPLACE(@name,' ','</n><n>') + '</n></f>' as xml)

;WITH cte AS (
SELECT  t.v.value('.','nvarchar(50)') as Names,
        CASE WHEN LEN(t.v.value('.','nvarchar(50)')) = 1 THEN 1 ELSE 0 END as LenName,
        row_number() over(order by t.v) as RowNumber
FROM @xml.nodes('/f/n') as t(v)
WHERE NULLIF(t.v.value('.','nvarchar(50)'),'') IS NOT NULL
)

SELECT 
    STUFF(
    (SELECT ' ' + NAMES
    FROM cte
    WHERE LenName = 0 OR (LenName = 1 and RowNumber = 1)
    FOR XML PATH('')),1,1,'') as FirstName,
    STUFF(
    (SELECT ' ' + NAMES
    FROM cte
    WHERE LenName = 1 AND RowNumber != 1
    FOR XML PATH('')),1,1,'') as MiddleInitial

For 'Mate Kate' or ' Mate Kate '

FirstName   MiddleInitial
Mate Kate   NULL

For 'Mate K' or 'Mate K '

FirstName   MiddleInitial
Mate        K

For ' M Kate ' or 'M Kate'

FirstName   MiddleInitial
M Kate      NULL

Upvotes: 0

Sandesh
Sandesh

Reputation: 1044

Hope this helps.

    declare @name as varchar(50)
set @name ='MATE    K'
select CASE WHEN CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))),0)=2 THEN left(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))))   ELSE   LTRIM(RTRIM(@name)) END AS firstname,
CASE  WHEN CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))),0)=2 THEN LTRIM(RTRIM(substring(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) +1, len(LTRIM(RTRIM(@name)))-(CHARINDEX(' ',LTRIM(RTRIM(@name)))-1))))  ELSE ''  END  as MiddleInitial

Upvotes: 0

mohan111
mohan111

Reputation: 8865

DECLARE @t table 
(name varchar(20))
INSERT INTO @t(name) values 
('MATE K'),
('MATE KATE')

select 
CASE WHEN LEN(substring(name, CHARINDEX(' ', name) +1, len(name)-(CHARINDEX(' ',name)-1))) > 1 
THEN 
NAME 
ELSE  left(name, CHARINDEX(' ', name)) END as FirstName,
CASE WHEN LEN(substring(name, CHARINDEX(' ', name) +1, len(name)-(CHARINDEX(' ',name)-1))) = 1
THEN 
substring(name, CHARINDEX(' ', name) +1, len(name)-(CHARINDEX(' ',name)-1)) ELSE NULL END as MiddleInitial
FROM @t

Upvotes: 0

Tanner
Tanner

Reputation: 22733

This should do the job, could probably be simplified though:

DECLARE @name AS VARCHAR(50)
SET @name = 'MATE K'

SELECT  CASE WHEN CHARINDEX(' ', @name) != LEN(@name) - 1 THEN @name
             ELSE LEFT(@name, CHARINDEX(' ', @name))
        END AS FirstName ,
        CASE WHEN CHARINDEX(' ', @name) = LEN(@name) - 1
             THEN SUBSTRING(@name, CHARINDEX(' ', @name) + 1, 1)
             ELSE ''
        END AS MiddleName

Output:
FirstName   MiddleName
MATE        K

SET @name = 'MATE KATE'

SELECT  CASE WHEN CHARINDEX(' ', @name) != LEN(@name) - 1 THEN @name
             ELSE LEFT(@name, CHARINDEX(' ', @name))
        END AS FirstName ,
        CASE WHEN CHARINDEX(' ', @name) = LEN(@name) - 1
             THEN SUBSTRING(@name, CHARINDEX(' ', @name) + 1, 1)
             ELSE ''
        END AS MiddleName

-- Output:
    Output:
FirstName   MiddleName
MATE KATE

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This will not consider all possible bad strings, but will give you a start point. And you better do this not in SQL but with some CLR. SQL is not really supposed to do things like this and is quite limited:

DECLARE @t TABLE ( s VARCHAR(MAX) )
INSERT  INTO @t
VALUES  ( 'MATE K' ),
        ( 'MATE KATE' )


SELECT  SUBSTRING(s, 1,
                  CASE WHEN SUBSTRING(REVERSE(s), 2, 1) = ' '
                       THEN CHARINDEX(' ', s) - 1
                       ELSE LEN(s)
                  END) AS FirstName ,
        CASE WHEN SUBSTRING(REVERSE(s), 2, 1) = ' '
             THEN SUBSTRING(s, LEN(s), 1)
             ELSE NULL
        END AS MiddleName
FROM    @t

Output:

FirstName   MiddleName
MATE        K
MATE KATE   NULL

Upvotes: 3

Related Questions