Link to home
Start Free TrialLog in
Avatar of Jacque Scott
Jacque ScottFlag for United States of America

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.

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

Open in new window


Any help is appreciated.
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie

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

Open in new window

Avatar of Jacque Scott

ASKER

Thank you for your quick response.  This works.