SQL Server Portal

Today, I  received an email from one of my friends. He was working on a legacy data and he executed a wrong update statement on a table. Basically, he removed the space between the names and then updated the Student name column. For example, if the student name was “Steve Masters”, it became “SteveMasters”.

Now, we need to insert a space before Capital letters to convert the data into its original state. As usual, I started the easy way… the web search but almost all solutions use loop to convert it. I normally avoid loop to perform any operation, unless it is the last option, due to performance issue.

So, I designed the solution via Xquery and inserted space before the capital letters without loop.

Let me create sample to explain it.

USE tempdb
GO
CREATE TABLE Student
(
[Student ID] INT Identity(1,1),
[Student Name] VARCHAR(50)
)
GO
INSERT INTO Student VALUES ('SteveMasters')
INSERT INTO Student VALUES ('DavidOrtiz')
INSERT INTO Student VALUES ('MichaelSeanRay')
INSERT INTO Student VALUES ('StevenSelikoff')
INSERT INTO Student VALUES ('CarolePoland')
INSERT INTO Student VALUES ('BjornRettig')
INSERT INTO Student VALUES ('MichikoOsada')
INSERT INTO Student VALUES ('CarolPhilips')
INSERT INTO Student VALUES ('MeravNetz')
GO
SELECT * FROM Student
GO
--OUTPUT

Given below is the user defined function that can insert the space before each capital letter.

USE tempdb
GO
CREATE FUNCTION dbo.[UDF_Space_Before_Capital_Letters]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;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_STRING=ISNULL(@RETURN_STRING,'')+
(CASE WHEN SUBSTRING(@String,Nums.n,1)=
UPPER(SUBSTRING(@String,Nums.n,1)) collate Latin1_General_CS_As
AND Nums.n >1
THEN SPACE(1) + SUBSTRING(@String,Nums.n,1)
ELSE SUBSTRING(@String,Nums.n,1) END)

FROM N4 Nums
WHERE Nums.n<=LEN(@String)

RETURN @RETURN_STRING
END
GO
-- For Example :
USE tempdb
GO
SELECT [Student ID]
,dbo.[UDF_Space_Before_Capital_Letters]([Student Name]) As [Student Name]
from Student
GO

Let me know if you come across this issue and its solution.