Hi ,

  The charindex can run this

LEFT([Description], CHARINDEX('(', [Description]) + 1)  as NewDesc, but I can't run

LEFT([Description], CHARINDEX('(', [Description]) - 1)  as NewDesc,

Does anybody knows why ?

Thanks

Do you get an error? What does CHARINDEX('(',[Description]) return?

The useful trick to avoid errors is to use NULLIF function, e.g.

LEFT([Description], NULLIF(CHARINDEX('(', [Description]),0) -1) as NewDesc

Do you have a '(' in your data? If Charindex does not find the character, it returns 0, you are then subtracting 1 and trying to do a LEFT(<field>, -1)

You cannot provide a negative value for LEFT RIGHT or SUBSTRING.

As Naomi points out, you could use a NULLIF and COALESCE to avoid this:

DECLARE @table TABLE (value VARCHAR(20))
INSERT INTO @table (value) VALUES
('pp(pp'),('pppp')


SELECT *, LEFT(value,CHARINDEX('(',value)+1), LEFT(value,COALESCE(NULLIF(CHARINDEX('(',value)-1,-1),LEN(value)))
  FROM @table

You are correct, I need to use NULLIF, because some records return 0.

Thanks a lot