April 11, 2015 at 10:25 pm
Hi,
In my staging table I am having data like below
ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
Now I want to find the Number of times a '¯'character appears in a string.
I should get output 14
thanks & regards,
Vipin Jha
April 11, 2015 at 11:38 pm
Two suggestions, first one is counting by subtracting the length of the string after removing all the X characters to the original length of the string, the second one is for fun.
😎
USE tempdb;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'dbo.TBL_DASHES') IS NOT NULL DROP TABLE dbo.TBL_DASHES;
CREATE TABLE dbo.TBL_DASHES
(
DH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_DASHES_DH_ID PRIMARY KEY CLUSTERED
,DH_STRING VARCHAR(200) NOT NULL
);
INSERT INTO dbo.TBL_DASHES (DH_STRING)
VALUES
('ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯ ' )
,('ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯' )
;
/* Method #1
Counting by subtracting the length of the string after
removing all the X characters to the original length
of the string.
*/
SELECT
TD.DH_ID
,LEN(X.ST) - LEN(REPLACE(X.ST,CHAR(175),'')) AS COUNT_CHR_175
,TD.DH_STRING
FROM dbo.TBL_DASHES TD
CROSS APPLY (SELECT CHAR(124)
+ TD.DH_STRING
+ CHAR(124)
) AS X(ST);
/* Method #2
Iterate through the string and mark each occurrance.
*/
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
SELECT
TD.DH_ID
,TD.DH_STRING
,SUM(CASE WHEN ASCII(SUBSTRING(TD.DH_STRING COLLATE Latin1_General_BIN,NM.N,1)) = 175 THEN 1 ELSE 0 END) AS COUNT_CHR_175
FROM dbo.TBL_DASHES TD
CROSS APPLY
(
SELECT TOP(LEN(TD.DH_STRING)) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4
) AS NM(N)
GROUP BY TD.DH_ID
,TD.DH_STRING;
Results
DH_ID COUNT_CHR_175 DH_STRING
----------- ------------- ------------------------------------------------
1 13 ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
2 13 ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
3 13 ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
4 13 ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
5 13 ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
6 13 ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
7 13 ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
8 14 ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
April 13, 2015 at 8:37 am
@Vipin, there are spaces between the dashes, thus I've removed them.
Simple Query:
CREATE TABLE #TBL_DASHES
(
DH_ID INT IDENTITY(1,1) NOT NULL
,DH_STRING VARCHAR(200) NOT NULL
);
INSERT INTO #TBL_DASHES (DH_STRING)
VALUES
('ABL¯ABL¯0¯0¯ABL¯¯¯¯¯¯¯¯¯')
,('ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯¯¯¯¯¯¯')
,('ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯¯¯¯¯')
,('ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯¯¯¯¯')
,('ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯¯¯¯¯¯')
,('ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯¯¯¯¯¯¯')
,('ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯¯¯¯¯¯¯')
,('ABL¯ALE¯1041¯1150¯DLS¯ALE¯¯¯¯¯¯¯¯¯')
;
select
DH_ID,
DH_STRING, len(DH_STRING) AS DH_STRING_COUNT,
REPLACE(DH_STRING, '¯', '') AS DH_STRING_NO_DASH, len(REPLACE(DH_STRING, '¯', '')) AS DH_STRING_NO_DASH_COUNT,
len(DH_STRING) - len(REPLACE(DH_STRING, '¯', '')) AS DASHES_COUNT
from #TBL_DASHES
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply