try:

select LEFT(name,CHARINDEX('|',name + '|')-1)
from table1

Think you're almost there - you just need to filter to ensure you aren't passing a negative number to the LEFT e.g.,

SELECT left(name, charindex('|',name) -1) from table1
WHERE name LIKE '%|%'

Remember charindex is 0 if it doesn't exist in string, then you subtract 1. This means you're trying to do a LEFT(name, -1) which is invalid.

To get your full output (with all rows, regardless of the | symbol) you can use it in a CASE instead.

SELECT CASE 
        WHEN [name] LIKE '%|%' THEN left(name, charindex('|',name) -1) 
        ELSE [name] END AS [name]
from table1

Edit: Here's a db<>fiddle with results.

You can try the below -

SELECT left(name, case when charindex('|',name)=0 then len(name)
else charindex('|',name)-1 end),charindex('|',name)
from tablename

based on your query, its expecting values to have | always. So this will error on name = jo smith'. Use select case to solve this.

declare @str varchar(200)
set @str = 'jo smith'

select case when charindex('|',@str) > 0 then left(@str, charindex('|',@str) -1) else @str end

Instead of using LEFT , use a combination of STUFF and ISNULL .

While LEFT will raise the "Invalid length parameter passed to the left function." error if it gets a negative length parameter,
STUFF will simply return null if it's starting position argument is less than less than 1.

This means that if the original value doesn't contain | and charindex return 0 , stuff will simply return null , and you can use ISNULL to return the original value in this case.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T TABLE  (
    [name] varchar(20)
);
    
INSERT INTO @T ([name]) VALUES
('jack| rose|'),
('wil |jo|rdy'),
('karl||jay'),
('jo smith'),
('jjill'),
('raj |kumar');

The query:

SELECT [name],
        ISNULL(STUFF([name], CHARINDEX('|', [name]), LEN([name]), ''), [name]) As Replaced
FROM @T

Results:

name            Replaced
jack| rose|     jack
wil |jo|rdy     wil 
karl||jay       karl
jo smith        jo smith
jjill           jjill
raj |kumar      raj

You can use this in a calculated field as:

(case when abs(charindex('-',[ColumnFormatted]))>(0) then substring([ColumnFormatted],(1),abs(charindex('-',[ColumnFormatted])-(1))) when abs(charindex(',',[ColumnFormatted]))>(0) then replace(substring([ColumnFormatted],(1),abs(charindex(',',[ColumnFormatted])-(1))),':','') else [ColumnFormatted] end)

Or as a SQL statement:

select (insert the above case statement here) as NewColummn