I am trying to create a new column by substracting two other columns NameVer and Ver. Basically the output should have a value of Name - Ver, e.g. Adam Philips-10021 - 10021 = Adam Philips (I also need the last dash before the Ver deleted).

I have a following table:

What I am trying to achieve is this table:

Strings as you can are see consisted of various characters, sometimes a dash occurs not only before the Ver part but also somewhere in the middle. Moreover, I have discovered that some of string in both columns have empty spaces beyond visible characters.

I have tried using the following code:

SELECT SUBSTRING(NameVer, 1, LEN(Ver) 1)
from myTable

unfortunately, instead of for instance Dr. Cristine Pharrel HF I got Dr. Cr

CodePudding user response：

You could do it using the following method:

SELECT SUBSTRING(NameVer,1, LEN(NameVer) - LEN(Ver) -1)
FROM myTable

CodePudding user response：

We can try using a plain REPLACE here:

SELECT
    NameVer,
    Ver,
    REPLACE(NameVer, '-'   Ver, '') AS Name
FROM myTable;

Demo