Menu
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.
923
This error message can easily be avoided by making sure that the integer value passed as the length to either the LEFT substring function or SUBSTRING string function is not negative. One way of checking it within the LEFT or SUBSTRING function is with the use of the CASE function.
SUBSTRING is a string manipulation function that manipulates all string data types (BIT, BLOB, and CHARACTER), and extracts characters from a string to create another string.
The SUBSTRING() function extracts a substring from a string (starting at any position). Note: The SUBSTR() and MID() functions equals to the SUBSTRING() function.
The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
