nth occurance of a alphabet

Question

Post reply

nth occurance of a alphabet

NLV

SSChasing Mays

Points: 654
More actions
May 2, 2012 at 6:46 am

#392169

Hi,
I have a table like below and I want to find the max occurrence of alphabet 'A' in a col3 and min occurrence of alphabet 'B' in col3.
Create table tbl1
(col1 varchar (20),
col2 varchar (20),
col3 varchar (20),
)
Insert into tbl1 Values('111','1w12','AABADSAAA')
Insert into tbl1 Values('222','2w22','XVBBBAABBA')
Insert into tbl1 Values('333','3w33','XVAABAAAAA')
Please help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26951 More actions · Answer 1

See if this helps

SELECT col1,col2,col3,

LEN(col3)-LEN(REPLACE(col3,'A','')) AS NumberOfA,

LEN(col3)-LEN(REPLACE(col3,'B','')) AS NumberOfB

FROM tbl1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

NLV SSChasing Mays Points: 654 More actions · Answer 2

NLV

SSChasing Mays

Points: 654

May 2, 2012 at 7:17 am

#1482359

Thanks Mark.

CapnHector SSCoach Points: 16143 More actions · Answer 3

since in your other thread you ask how to build a tally table i thought i would give you one method to do it with a tally table. it is much slower than the LEN() - LEN(REPLACE()) but shows one solution how a tally table could be used in this situation.

Here is the query, it generates a substring of each character in the string and if it is a (or b) makes it a 1 otherwise makes it a 0 then sums the resulting column to get the number of times the letter appears:

SELECT col,

SUM(CASE WHEN SUBSTRING(col,N,1) = 'A' THEN 1 ELSE 0 END),

SUM(CASE WHEN SUBSTRING(col,N,1) = 'B' THEN 1 ELSE 0 END)

FROM RandString

CROSS JOIN Tally -- my tally table to 11000

WHERE N <= LEN(col) -- limit it so we are not trying to get a sub string after the string length

GROUP BY col

and here are the results over my 1 million GUID table

----======Len() - LEN(REPLACE())======-------
SQL Server Execution Times:
CPU time = 2450 ms, elapsed time = 2451 ms.
----======Tally with SUM(CASE)======-------
SQL Server Execution Times:
CPU time = 36239 ms, elapsed time = 36364 ms.

and finally the code i used to get the results:

--Generate the data

SELECT TOP 1000000 NEWID() AS col INTO RandString

FROM Tally a, Tally b -- could also use sys.all_columns

--Make things quiet so we dont have the time to display

DECLARE @a INT

DECLARE @b-2 INT

DECLARE @col CHAR(36)

PRINT '----======Len() - LEN(REPLACE())======-------'

SET STATISTICS TIME ON

SELECT @col = col,

@a = LEN(col)-LEN(REPLACE(col,'A','')),

@b-2 = LEN(col)-LEN(REPLACE(col,'B',''))

FROM RandString

SET STATISTICS TIME OFF

PRINT '----======Tally with SUM(CASE)======-------'

SET STATISTICS TIME ON

SELECT @col = col,

@a = SUM(CASE WHEN SUBSTRING(col,N,1) = 'A' THEN 1 ELSE 0 END),

@b-2 = SUM(CASE WHEN SUBSTRING(col,N,1) = 'B' THEN 1 ELSE 0 END)

FROM RandString

CROSS JOIN Tally -- my tally table to 11000

WHERE N <= LEN(col) -- limit it so we are not trying to get a sub string after the string length

GROUP BY col

SET STATISTICS TIME OFF

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 4

When you say "max occurence of alphabet 'A'", do you mean you want the position in the string of the last "A" in it?

If so, something like this will find that:

DECLARE @String VARCHAR(100) = 'xxxxAxxxxAxxxAx ' ;

SELECT LEN(RTRIM(@String)) - CHARINDEX('A', REVERSE(RTRIM(@String))) + 1 ;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

to find the first instance of a character in a string use "charindex"

to find the last instance use a combination of charindex and REVERSE

select LEN(col3) - CHARINDEX('A',REVERSE,col3)) as last_a, CHARINDEX('B',col3)) as first_b

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

May 2, 2012 at 8:17 am

#1482409

+1 GSquared - he beat me to it

MVDBA

GSquared SSC Guru Points: 260824 More actions · Answer 7

michael vessey (5/2/2012)
to find the first instance of a character in a string use "charindex"
to find the last instance use a combination of charindex and REVERSE
select LEN(col3) - CHARINDEX('A',REVERSE,col3)) as last_a, CHARINDEX('B',col3)) as first_b

You'll need to add 1 to the result of your final occurence math. Try it on a string that's nothing but the string you're looking for, you'll find out why.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon