November 16, 2016 at 12:58 am
For fun, appended the three characters to the previous sample and into the BASE_DATA CTE
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_STRING VARCHAR(2000) = 'Lorem ipsum dolor sit amet, CONSECtetur adipiSCIng elit. PelleNTesque in ELIT ut ex accumsan sagittis in a urna. ' + CHAR(196) + CHAR(197) + CHAR(214);
DECLARE @DESIRED_OUTPUT VARCHAR(2000) = 'Lorem ipsum dolor sit amet, <b>consec</b>tetur adipi<b>sci</b>ng elit. PelleNTesque in <b>elit</b> ut ex accumsan sagittis in a urna. <b>äåö</b>';
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(LEN(@SAMPLE_STRING)) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4)
,BASE_DATA AS
(
SELECT
NM.N
,SUBSTRING(@SAMPLE_STRING,NM.N,1) AS CHR_VAL
,CASE
WHEN ASCII(SUBSTRING(@SAMPLE_STRING,NM.N,1)) BETWEEN 65 AND 90 THEN 1
WHEN ASCII(SUBSTRING(@SAMPLE_STRING,NM.N,1)) = 196 THEN 1
WHEN ASCII(SUBSTRING(@SAMPLE_STRING,NM.N,1)) = 197 THEN 1
WHEN ASCII(SUBSTRING(@SAMPLE_STRING,NM.N,1)) = 214 THEN 1
ELSE 0
END AS IS_CAPS
FROM NUMS NM
)
,CHANGE_IDENTIFICATION AS
(
SELECT
BD.N
,BD.CHR_VAL
,BD.IS_CAPS
,CASE WHEN BD.IS_CAPS
= LAG(BD.IS_CAPS,1,BD.IS_CAPS) OVER
(
ORDER BY BD.N
) THEN 0 ELSE 1 END AS IS_CHANGED
FROM BASE_DATA BD
)
,GROUPED_SET AS
(
SELECT
CI.N
,CI.CHR_VAL
,CI.IS_CAPS
,SUM(CI.IS_CHANGED) OVER
(
ORDER BY CI.N
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GRP_ID
FROM CHANGE_IDENTIFICATION CI
)
,GROUPS_AND_SIZES AS
(
SELECT
GS.GRP_ID
,MAX(GS.IS_CAPS) AS IS_CAPS
,COUNT(GS.N) AS GRP_SIZE
,(SELECT
'' + SGS.CHR_VAL
FROM GROUPED_SET SGS
WHERE GS.GRP_ID = SGS.GRP_ID
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(2000)') AS GR_STR
FROM GROUPED_SET GS
GROUP BY GS.GRP_ID
)
SELECT 'INPUT' AS STR_NAME, @SAMPLE_STRING AS OUTPUT_STRING UNION ALL
SELECT 'OUTPUT' AS STR_NAME,
(
SELECT
CASE
WHEN GAS.IS_CAPS = 1 AND GAS.GRP_SIZE > 2 THEN '<b>' + LOWER(GAS.GR_STR) + '</b>'
ELSE GAS.GR_STR
END
FROM GROUPS_AND_SIZES GAS
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(2000)') AS OUTPUT_STR
UNION ALL
SELECT 'DESIRED' AS STR_NAME, @DESIRED_OUTPUT AS OUTPUT_STR;
Output
STR_NAME OUTPUT_STRING
-------- --------------------------------------------------------------------------------------------------------------------------------------------------
INPUT Lorem ipsum dolor sit amet, CONSECtetur adipiSCIng elit. PelleNTesque in ELIT ut ex accumsan sagittis in a urna. ÄÅÖ
OUTPUT Lorem ipsum dolor sit amet, <b>consec</b>tetur adipi<b>sci</b>ng elit. PelleNTesque in <b>elit</b> ut ex accumsan sagittis in a urna. <b>äåö</b>
DESIRED Lorem ipsum dolor sit amet, <b>consec</b>tetur adipi<b>sci</b>ng elit. PelleNTesque in <b>elit</b> ut ex accumsan sagittis in a urna. <b>äåö</b>
November 16, 2016 at 8:00 am
I still prefer the Pattern splitter.
DECLARE @String varchar(1000);
SET @String = 'Lorem ipsum dolor sit amet, CONSECtetur adipiSCIng elit. PelleNTesque in ELIT ut ex accumsan sagittis in a urna. ÄÅÖ'
SELECT ( SELECT CASE WHEN ItemNumber > 1 AND Matched = 1 AND LEN(Item) > 2
THEN '<b>' + LOWER(Item) + '</b>'
ELSE Item END
FROM dbo.PatternSplitCM(@String, '%[A-ZÄÅÖ]%' )x
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)');
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply