How can I write an SQL Server query to trim all lowercase characters that comes before the first uppercase character in a string? e.g. string value “eaplgCostPrice”, remove “eaplg” and pass “CostPrice” to a new column
Advertisement
Answer
If PATINDEX is used in combination with certain collations then it can function case dependent.
Example snippet:
--
-- using a table variable for demonstration purposes
--
declare @Table table (id int identity(1,1), col varchar(30));
insert into @Table (col) values ('eaplgCostPrice'),('SellPrice'),('amount');
--
-- trim leading lowercases when needed
--
select col,
(case
when patindex('[a-z]%[a-z][A-Z]%', col COLLATE Latin1_General_Bin) > 0
then substring(col, patindex('%[A-Z]%', col COLLATE Latin1_General_Bin), len(col))
else col
end) as trimmedCol
from @Table
Result:
col trimmedCol ------------------ ---------------- eaplgCostPrice CostPrice SellPrice SellPrice amount amount