You really should redesign this table to hold firstname, lastname, and commercial name in separate columns. And actually you should go a step further and create two separate tables one for people and one for companies - have a look on this site for articles about normalization for more about that.But, that said, here you go: select CASE WHEN charindex(',',[name]) > 0 THEN substring([name], 1, charindex(',',[name])-1) ELSE [name] END as lastname , CASE WHEN charindex(',',[name]) > 0 THEN substring([name], charindex(',',[name])+2, len([name])) ELSE '' END as firstnamefrom account