question

TCurry avatar image
TCurry asked

How do I extract the first, middle and last name as 1 column from a column formatted: [Group] FIRST MIDDLE LAST

I'm trying to join two tables one has a column with the full name and the other has a column with group and then full name, please see below to get an idea of data and format:

[JEDI] Mace Windu

[SITH] Darth Maul

[JEDI] Obi-Wan Kenobi

[JEDI] Anakin Skywalker

[JEDI] Yoda

[SITH] Ulic Qel-Droma

[IMP] Orson Callan Krennic

So the result I am looking for is:

Mace Windu

Darth Maul

Obi-Wan Kenobi

Anakin Skywalker

Yoda

Ulic Qel-Droma

Orson Callan Krennic

I'll then be able to use this to join on and link to the other table which had full name only

joins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

A couple of options come to mind:

1. JOIN on LIKE. eg `SELECT... FROM t1 INNER JOIN t2 ON t1.GroupAndName LIKE '%'+t2.NameOnly`

2. JOIN with string manipulation. eg `SELECT ... FROM t1 INNER JOIN t2 ON t2.Nameonly = STUFF(t1.GroupAndName, 1, CHARINDEX(']', t1.GroupAndName) + 1, '')

Something like that, anyway. Untested code, so you might need to fiddle a bit to get them working properly.

NB: Neither of these will perform particularly well at scale.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

lokeshlehkara avatar image
lokeshlehkara answered

Create a CTE which will have a calculated column after removing group from the name.

Expression used SUBSTRING(FullName,CHARINDEX(']', FullName)+1,LEN(FullName))

Then join this CTE with original table on Fullname.


one.jpg (46.1 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.