Invalid length parameter passed to the LEFT or SUBSTRING function

t-sql

Solution

Chances are good you have rows where the '-' is missing, which is causing your error. Try this...

SELECT i.Itemid,
    SUBSTRING(i.ShortDescription, 22, CHARINDEX('-', i.ShortDescription+'-', 22)) AS ProductDescriptionAbbrev,
FROM t_items i

Problem

I have the following description: 'Sample Product Maker Product Name XYZ - Size' and I would like to only get the value 'Product Name XYZ' from this. If this were just one row I'd have no issue just using SUBSTRING but I have thousands of records and although the initial value Sample Product Maker is the same for all products the Product Name could be different and I don't want anything after the hyphen. What I have so far has generated the error in the header of this question. ``` SELECT i.Itemid, RTRIM(LTRIM(SUBSTRING(i.ShortDescription, 25, (SUBSTRING(i.ShortDescription, 25, CHARINDEX('-', i.ShortDescription, 25)))))) AS ProductDescriptionAbbrev, CHARINDEX('-', i.ShortDescription, 0) - 25 as charindexpos FROM t_items i ``` I am getting 'Argument data type varchar is invalid for argument 3 of substring function' As you can see, I am getting the value for the last line the sql statement but when I try and plug that into the SUBSTRING function I get various issues.

Original source