CREATE TABLE #SimpleText (
FirstName VARCHAR(20),
LastName VARCHAR(20)
);
INSERT #SimpleText(FirstName, LastName) VALUES
('bob', 'jones'),
('Alice', 'Cooper'),
('matt', 'Miller'),
('Steve', 'jones');
-- if all you want is detection, this should work.
SELECT
st.FirstName,
st.LastName,
is_FN_cap = CASE WHEN ASCII(SUBSTRING(st.FirstName, 1, 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END,
is_LN_cap = CASE WHEN ASCII(SUBSTRING(st.LastName, 1, 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END
FROM
#SimpleText st;
-- IF you want TO actually make a correction, you can do something like this.
SELECT
st.FirstName,
st.LastName,
ic.is_FN_cap,
ic.is_LN_cap,
f_fix = STUFF(st.FirstName, 1, 1, u.f_upper),
l_fix = STUFF(st.LastName, 1, 1, u.l_upper)
FROM
#SimpleText st
CROSS APPLY ( VALUES (SUBSTRING(st.FirstName, 1, 1), SUBSTRING(st.LastName, 1, 1)) ) fl (f_initial, l_initial)
CROSS APPLY ( VALUES (
CASE WHEN ASCII(fl.f_initial) BETWEEN 65 AND 90 THEN 1 ELSE 0 END,
CASE WHEN ASCII(fl.l_initial) BETWEEN 65 AND 90 THEN 1 ELSE 0 END
) ) ic (is_FN_cap, is_LN_cap)
CROSS APPLY ( VALUES (
CASE WHEN ASCII(fl.f_initial) BETWEEN 65 AND 90 THEN fl.f_initial ELSE CHAR(ASCII(fl.f_initial) - 32) END,
CASE WHEN ASCII(fl.l_initial) BETWEEN 65 AND 90 THEN fl.l_initial ELSE CHAR(ASCII(fl.l_initial) - 32) END
) ) u (f_upper, l_upper);
First result set...
FirstName LastName is_FN_cap is_LN_cap
-------------------- -------------------- ----------- -----------
bob jones 0 0
Alice Cooper 1 1
matt Miller 0 1
Steve jones 1 0
Second result set...
FirstName LastName is_FN_cap is_LN_cap f_fix l_fix
-------------------- -------------------- ----------- ----------- -------------------- --------------------
bob jones 0 0 Bob Jones
Alice Cooper 1 1 Alice Cooper
matt Miller 0 1 Matt Miller
Steve jones 1 0 Steve Jones