Extracting characters only in SQL Server

Question

I have the following sample data in a SQL Server 2014 database

• xxxx,yyyy: 679459
• xxxx,yyyy:679459
• xxxx,yyyy : 679459
• xxxx,yyyy :679459

As you can see there is no uniformity of the way the data is written and is written in any of the above formats.

I'm trying to only extract the last and first names i.e. xxxx,yyyy

From some internet searches I found the following and tried it

left(EMP_CLass_9_descr, isnull(nullif(charindex(' :', EMP_CLass_9_descr),0) - 1,8000)) As TM_NAME

This only worked where there is a space before the :

I also tried

left(EMP_CLass_9_descr, isnull(nullif(charindex(':', EMP_CLass_9_descr),0) - 1,8000)) As TM_NAME

and that then includes a space in the scenario where it's written as

• xxxx,yyyy :679459

Is there is way that this can be written where I only extract the xxxx,yyyy?

Thanks in advance for any help you can provide.

Answer 1

Rasmac, You were almost there. Try this:

left(EMP_CLass_9_descr, isnull(nullif(charindex(':', REPLACE(EMP_CLass_9_descr,' ','')),0) - 1,8000)) As TM_NAME