Jacque Scott
asked on
Substring() and LEFT() syntax
I have a column and it has a CHAR(10) in the string. I need to extract everything before the CHAR(10). I can do that but it leaves the CHAR(10). When I use a -1 I get an error,
"Invalid length parameter passed to the LEFT or SUBSTRING function."
The field is a text field and that is I am using CAST().
Below is what I am using.
Any help is appreciated.
"Invalid length parameter passed to the LEFT or SUBSTRING function."
The field is a text field and that is I am using CAST().
Below is what I am using.
SELECT cte.cpDescription AS cpDesc, Substring(CAST(m.Description AS varchar(250)), 1, Charindex(CHAR(10), m.Description) -1)
FROM cte
JOIN Matters m ON cte.Matters = m.Matters
Any help is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is CHAR(10) definitely being found in the field Description of every record returned by the query?
Well, I see I left out a THEN in the query... revision follows.
SELECT cte.cpDescription AS cpDesc,
CASE Charindex(CHAR(10), m.Description)
WHEN 0 Then CAST(m.Description AS varchar(250)
WHEN 1 Then Substring(CAST(m.Description AS varchar(250)), 2)
ELSE Substring(CAST(m.Description AS varchar(250)), 1, Charindex(CHAR(10), m.Description) -1)
END
FROM cte
JOIN Matters m ON cte.Matters = m.Matters
ASKER
Thank you for your quick response. This works.