Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
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
                The error reported (Argument data type varchar is invalid for argument 3 of substring function) is based on the syntax of the OP's SQL statement, not the retrieved data.
– HABO
                Oct 23, 2012 at 21:59
                Don't look for the offending row just in the retrieved data.  It can be in the underlying dataset before the WHERE clause.  That makes it difficult to find offending row but this method solves it in either event.
– pghcpa
                Sep 1, 2014 at 21:10
You could also strip out the Sample Product Maker text and go from there:
SELECT RTRIM(LEFT(
    LTRIM(REPLACE(i.ShortDescription, 'Sample Product Maker', '')),
        CHARINDEX('-', LTRIM(REPLACE(i.ShortDescription, 'Sample Product Maker', 
    '' ))) - 1))
AS ShortDescription
                This gets me the EXACT same issue I had before and it's not really what I was asking for....
– brianhevans
                Oct 23, 2012 at 21:07
                No it doesn't.  This gives you the correct result.  Your original problem was using a string instead of an int for the 3rd parm of the first SUBSTRING.  And, starting from position 25 instead of 22.
– GilM
                Oct 23, 2012 at 21:38
                The answer to this was based in the fact that some of my strings didn't have a "-" so I was returning a negative value to the substring and that won't work.  It's too bad there was a better error descriptor.  thanks
– brianhevans
                Oct 29, 2012 at 2:08
Your first call to SUBSTRING specifies a length of SUBSTRING(i.ShortDescription, 25, CHARINDEX('-', i.ShortDescription, 25)).
You might try:
declare @t_items as Table ( ItemId Int Identity, ShortDescription VarChar(100) )
insert into @t_items ( ShortDescription ) values
  ( 'Sample Product Maker Product Name XYZ - Size' )
declare @SkipLength as Int = Len( 'Sample Product Maker' )
select ItemId,
  RTrim( LTrim( Substring( ShortDescription, @SkipLength + 1, CharIndex( '-', ShortDescription, @SkipLength ) - @SkipLength - 1 ) ) ) as ProductDescriptionAbbrev
  from @t_items
                Is that an answer?  I have been trying all sorts of things to get this to work.  Ideally I would have the charindexpos as the third argument of the preceding statement but that didn't work either.
– brianhevans
                Oct 23, 2012 at 21:00
The problem is that your outer call to SUBSTRING is being passed a character data type from the inner SUBSTRING call in the third parameter.
                                                  +--This call does not return an integer type
SELECT i.Itemid,                                  V
   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
The third parameter must evaluate to the length that you want.  Perhaps you meant LEN(SUBSTRING(...))?
SELECT i.Itemid, 
       RTRIM(LTRIM(SUBSTRING(i.ShortDescription, 22, CHARINDEX('-', i.ShortDescription)-22))) AS ProductDescriptionAbbrev, 
       CHARINDEX('-', i.ShortDescription)-22 as charindexpos
FROM t_items i 
You want:
LEFT(i.ShortDescription, isnull(nullif(CHARINDEX('-', i.ShortDescription),0) - 1, 8000))
Note that a good practice is to wrap charindex(...)'s and patindex(...)'s with nullif(...,0), and then handle the null case if desired (sometimes null is the right result, in this case we want all the text so we isnull(...,8000) for the length we want).
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.