T-SQL: Trim after newline / carriage return

USE [db_mes_sandbox]
GO

EXEC [dbo].[usp_CompleteHierarchy3]
@pSerial_number = N’110160766′,
@pFlag_IncludeCcaComponents = 0,
@pFlag_IncludeReplacedParts = 0,
@pDebug = 1
GO

SELECT
product_id
,name
,revision
,description = /* This CASE statement returns the first line of description when it contains the word ‘alternate’. */
CASE
WHEN CHARINDEX(CHAR(10),REPLACE(description,CHAR(13),CHAR(10)),1) > 0 /*Find values with multiple lines,*/
AND PATINDEX(‘%Alternate%’,description) > 0 /*and, find values containing the word ‘alternate’.*/
THEN LEFT(REPLACE(description,CHAR(13),CHAR(10)), CHARINDEX(CHAR(10),REPLACE(description,CHAR(13),CHAR(10)),1)-1) /*Use only the first line.*/
ELSE description
END
,raw_description = description
FROM qs_products
WHERE flags&1=0
–AND name=’266698-1′

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *