Removing anything after first newline in SQL Server select statement-CodePudding

I have a column 'Name' that contains newlines. We want to remove the newline as well as anything after the first instance of a newline.

The first step I did was replacing a newline with an empty string and it worked:

REPLACE(REPLACE(Name, CHAR(13), ''), CHAR(10), '')

Then, I tried replacing any newline with a '----' instead so I can use it with charindex() and only get anything left of it:

LEFT(REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'), CHARINDEX('----',REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'))-1)

This gives an error "Invalid length parameter passed to the LEFT or SUBSTRING function." which is to be expected because not all the names have a newline.

To account for those that dont have a new line I used substring() and case() wherein if it has no newline it will the substring length of the original else it will use charindex('----',...) instead.

  SUBSTRING(REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'),1,
  CASE WHEN CHARINDEX('----',REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----')) = 0 THEN LEN(REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'))
  ELSE CHARINDEX('----',REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'))-1 END)

Is there a more intuitive way of going about this where I don't have to replace the newlines with a string using that string as a reference point and using the newlines as a reference point instead?

CodePudding user response：

You aren't handling values that don't have a carriage return. As a result the position returned by CHARINDEX would be 0, and you can't have the -1 left most characters.

Add a carriage return to the end of your string, and then get the CHARINDEX:

LEFT(YourColumn, CHARINDEX(CHAR(13), YourColumn   CHAR(13))