sql - values after the 1st and 2nd space

Question

I'd like to break down a field into 3 parts, using the spaces ' '

Example

select name from nametable;

All the information is in the same field. eg 'dr john smith', 'mrs jane smith'.

I'd like the results to be broken up into 3 columns:

select title, firstname, lastname from nametable

eg:

'dr' as title, 'john' as firstname, 'smith' as lastname

'mrs' as title, 'jane' as firstname, 'smith' as lastname

TheEsnSiavashi TheEsnSiavashi · Accepted Answer · 2017-08-07T04:00:04

You can use parsename() function, like this:

declare @name varchar(100) = 'Mr John Nash'

select  parsename(Replace(@name , ' ', '.'), 3) as title,
        parsename(Replace(@name, ' ', '.'), 2) as firstName,
        parsename(Replace(@name, ' ', '.'), 1) as lastName;

In case you have periods in your @name, you can use this:

declare @name varchar(100) = 'Mr. John Nash'


select  Replace(parsename(Replace(Replace(@name, '.', '*') , ' ', '.'), 3), '*', '.') as title,
        Replace(parsename(Replace(Replace(@name, '.', '*'), ' ', '.'), 2), '*', '.') as firstName,
        Replace(parsename(Replace(Replace(@name, '.', '*'), ' ', '.'), 1), '*', '.') as lastName;

Here you first replace the . with a symbol that you never see, for example a *. and at the end you replace that * with a . again.

sql - values after the 1st and 2nd space

1 Answers