CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 1559

Parsing first middle and last name

I have a query that parses first, middle and last name from complete name.

DECLARE @name VARCHAR(20) = 'martin'

SELECT first_name = (
        CASE LEN(REPLACE(UPPER(@name), ' ', ''))
            WHEN LEN(UPPER(@name)) - 1
                THEN PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 2)
            ELSE PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 3)
            END
        )
    ,mi = (
        CASE LEN(REPLACE(UPPER(@name), ' ', ''))
            WHEN LEN(UPPER(@name)) - 1
                THEN NULL
            ELSE PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 2)
            END
        )
    ,last_name = (PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 1))

So the problem is, this query works fine when both first and last name are entered. Like @name = 'Martin Joe' works fine. But when the input is a single name @name = 'Martin',it is making that as last name and throwing first and MI as null.

Current output:

@name = 'Martin'
First name - Null
MI - Null
Last name - Martin

Desired output:

@name = 'Martin'
First name - Martin
MI - Null
Last name - Null

it is supposed to show firstname as 'Martin, MI and Lastname as null. Any help?!

Upvotes: 1

Views: 154

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

You need an additional WHEN in your first_name CASE to account for the collapsed string and the original string being the same length, so that situation will be treated as a first name. Then there has to be a CASE on the last_name to insure that the collapsed string and the original string are not the same length.

This seems to work:

DECLARE @name VARCHAR(20) = 'martin'


SELECT first_name = (
        CASE LEN(REPLACE(UPPER(@name), ' ', ''))
            WHEN LEN(UPPER(@name)) - 1
                THEN PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 2)
            WHEN LEN(@name) 
                THEN PARSENAME('..' + UPPER(@name), 1)
            ELSE PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 3)
            END
        )
    ,mi = (
        CASE LEN(REPLACE(UPPER(@name), ' ', ''))
            WHEN LEN(UPPER(@name)) - 1
                THEN NULL
            ELSE PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 2)
            END
        )
    ,last_name = 
        CASE 
          WHEN LEN(REPLACE(UPPER(@name), ' ', '')) < LEN(UPPER(@name))
              THEN (PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 1))
          ELSE NULL
        END

Upvotes: 0

sniperd
sniperd

Reputation: 5274

Sounds like you just want the query order reversed (first and last name flipped), does this do it for you?

DECLARE @name VARCHAR(20) = 'Martin Joe'

SELECT last_name = (
        CASE LEN(REPLACE(UPPER(@name), ' ', ''))
            WHEN LEN(UPPER(@name)) - 1
                THEN PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 2)
            ELSE PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 3)
            END
        )
    ,mi = (
        CASE LEN(REPLACE(UPPER(@name), ' ', ''))
            WHEN LEN(UPPER(@name)) - 1
                THEN NULL
            ELSE PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 2)
            END
        )
    ,first_name = (PARSENAME(REPLACE(UPPER(@name), ' ', '.'), 1))

This input 

DECLARE @name VARCHAR(20) = 'Martin Joe'

Would give (I'm guessing Joe is supposed to be fname in this case?)

lname   mi      fname
MARTIN  NULL    JOE

And this (now Martin is the first name in this case?)

DECLARE @name VARCHAR(20) = 'Martin'

Would give:

lname   mi      fname
NULL    NULL    MARTIN

On a side note, I took the original SQL code in question and used: http://poorsql.com/ to format it, it's handy :)

Upvotes: 1

Related Questions