SQL Server Portal

I received this query from one of my consultants, when he was busy migrating legacy data for one of our customers, enquiring how to check if some of the employee job titles have carriage return and line feed? Whether there is any solution I can provide to check in any string that it has carriage return and line feed or not. This is the first time, I came across this query so I started searching the solution, but could not get a proper solution. So I started developing the solution. Given below is the script.

SOLUTION :

--This script is compatible with SQL Server 2005 and above.
--DROP FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
--GO
CREATE FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
(
@String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_BOOLEAN INT

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_BOOLEAN =COUNT(*)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND ASCII(SUBSTRING(@String,Nums.n,1))
IN (13,10)

RETURN (CASE WHEN @RETURN_BOOLEAN >0 THEN 'TRUE' ELSE 'FALSE' END)
END
GO

EXAMPLE :
In this example, I manually inserted a carriage return and line feed in employee table to test the above solution using given below script.

USE AdventureWorks2012
GO
UPDATE [HumanResources].[Employee] SET [JobTitle] ='Research
and
Development Manager' WHERE BusinessEntityID =6

Lets browse the employee table using above solution and check. Given below is the script.

USE AdventureWorks2012
GO
SELECT BusinessEntityID,OrganizationLevel,JobTitle
,dbo.[UDF_Check_existance_of_carriage_return_line_feed] ([JobTitle])
AS [Boolean]
FROM HumanResources.Employee
--OUTPUT

Read Full Post »