Parse String to find a letter...

Question

Post reply

Parse String to find a letter...

apat

SSCertifiable

Points: 5929
More actions
May 20, 2011 at 9:33 am

#251327

Hi All,
One of the columns of my table include strings like below,
----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!
------Afkgf667788!!!---00flflsklsllfgldfgkflgk--111!!!!
Each string will ideally have atleast 1 or more 'A' in it. I need to find out how many "A" are there in each string.
Can anyone please help me with this?

Viewing 14 posts - 1 through 13 (of 13 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Something like this?

declare @MyString varchar(max) = '----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!'

select len(@MyString) - len(replace(@MyString, 'a', ''))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

apat SSCertifiable Points: 5929 More actions · Answer 2

apat

SSCertifiable

Points: 5929

May 20, 2011 at 10:53 am

#1327832

Thanks...

jarid.lawson SSCrazy Points: 2953 More actions · Answer 3

It's cumbersome, but it works. It is also scalable to any number of rows you need to scan.

If Exists

(Select *

From Sys.Tables

Where Name='A_Counter'

And Type='U')

Begin

Drop Table dbo.A_Counter

End

Go

--------------------

Create Table dbo.A_Counter

(CountValue Int,

ParseString Varchar(500),

ID_Field Int

Identity(1,1))

Go

------------------------------

Insert Into dbo.A_Counter

(CountValue,

ParseString)

Select 0,'----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!'

Union All

Select 0,'----A654064506870A6504654064A65@@@@@---------6540AA65046!'

Go

------------------------------

Declare @LoopCount Int,

@MaxLoopCount Int,

@ParseString Varchar(100),

@Step_Counter Int,

@A_Counter Int,

@MaxStep_Counter Int

----------

Set @LoopCount=1

Set @MaxLoopCount=

(Select Max(ID_Field)

From dbo.A_Counter)

----------

While @LoopCount<=@MaxLoopCount

Begin

----------

Set @ParseString=

(Select ParseString

From dbo.A_Counter

Where ID_Field=@LoopCount)

Set @Step_Counter=1

Set @MaxStep_Counter=Len(@ParseString)

----------

While @Step_Counter<=@MaxStep_Counter

Begin

If SubString(@ParseString,@Step_Counter,1)='A'

Begin

Set @A_Counter=

(Select CountValue

From dbo.A_Counter

Where ID_Field=@LoopCount)

----------

Update dbo.A_Counter

Set CountValue=@A_Counter + 1

Where ID_Field=@LoopCount

End

----------

Set @Step_Counter=@Step_Counter + 1

End

--------------------

Set @LoopCount=@LoopCount + 1

End

Go

------------------------------

Select *

From dbo.A_Counter

Results Set:

CountValue | ParseString | ID_Field

2 | ----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---! | 1

5 | ----A654064506870A6504654064A65@@@@@---------6540AA65046! | 2

I would love to see different / faster ways to do this.

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

jarid.lawson (5/20/2011)
I would love to see different / faster ways to do this.

Looping is definitely not the way to go about this. It is painfully slow. If you are trying to get a total count of occurrences across multiple rows just use a sum with the approach I presented before.

I changed this to use a temp table instead of a permanent table.

Create Table #A_Counter

(

CountValue Int,

ParseString Varchar(500)

)

Insert Into #A_Counter (CountValue, ParseString)

values (0,'----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!'),

(0,'----A654064506870A6504654064A65@@@@@---------6540AA65046!')

select sum(len(ParseString) - len(replace(ParseString, 'a', '')))

from #A_Counter

drop table #A_Counter

This is scalable AND fast!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jarid.lawson SSCrazy Points: 2953 More actions · Answer 5

I didn't catch what you did the first time around...that is brilliant!!!

I admit I am stuck on using loops too often. Part of why I'm on here is to learn better ways to handle issues. Thank you for getting me a couple steps further down the road. 😀

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Hanging around here long enough has gotten me down that road a little ways too. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95460 More actions · Answer 7

Sean Lange (5/20/2011)
Something like this?
declare @MyString varchar(max) = '----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!'
select len(@MyString) - len(replace(@MyString, 'a', ''))

Very nice Sean.

One thing to point out (for all solutions for this) is that if you have a case-sensitive collation, you will want to force the strings to be all upper (or lower, your choice) for that replace.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 8

jarid.lawson (5/20/2011)
I didn't catch what you did the first time around...that is brilliant!!!
I admit I am stuck on using loops too often. Part of why I'm on here is to learn better ways to handle issues. Thank you for getting me a couple steps further down the road. 😀

To borrow from Jeff Moden "stop thinking about what you want to do to the row... think about what you want to do to the column".

I say to try any solution as if T-SQL doesn't have support for "WHILE". (Makes loops almost impossible...)

Try out solutions to any question you can (don't look at other answers first), then compare to how the answers are. Learn how the tally table can eliminate loops. Soon enough, you'll have set-based guns in your holster instead of those loops...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

WayneS (5/20/2011)
Sean Lange (5/20/2011)
Something like this?
declare @MyString varchar(max) = '----A375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!'
select len(@MyString) - len(replace(@MyString, 'a', ''))
Very nice Sean.
One thing to point out (for all solutions for this) is that if you have a case-sensitive collation, you will want to force the strings to be all upper (or lower, your choice) for that replace.

Thanks Wayne. Your comment about case sensitivity got me to thinking about making this be a case sensitive count.

I think this should work. Notice I change the leading A to aaa.

declare @MyString varchar(max) = '----aaa375475960dkgjfkfkgjfkvkl!!!!!-------DAfkdfk55500---!'

select len(@MyString) - len(replace(@MyString collate SQL_Latin1_General_Cp1_CS_AS, 'a' collate SQL_Latin1_General_Cp1_CS_AS, ''))

select len(@MyString) - len(replace(@MyString collate SQL_Latin1_General_Cp1_CS_AS, 'A' collate SQL_Latin1_General_Cp1_CS_AS, ''))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95460 More actions · Answer 10

Ahh yes, much better. Excellent job Sean!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jarid.lawson SSCrazy Points: 2953 More actions · Answer 11

Btw, thank you for the link regarding the Tally table. That is already going to save a HUGE amount of time, and I only have scratched the surface on it.

I laugh because after I see the answer it seems so obvious, and I can't figure out why I hadn't thought of that before...lol

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

WayneS (5/20/2011)
Very nice Sean.
One thing to point out (for all solutions for this) is that if you have a case-sensitive collation, you will want to force the strings to be all upper (or lower, your choice) for that replace.

Better yet, use a Binary Collation. It's faster and will also take out accented characters which may be mistaken for an "A" or "a" in some default colations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 13

Jeff Moden (5/20/2011)
WayneS (5/20/2011)
Very nice Sean.
One thing to point out (for all solutions for this) is that if you have a case-sensitive collation, you will want to force the strings to be all upper (or lower, your choice) for that replace.
Better yet, use a Binary Collation. It's faster and will also take out accented characters which may be mistaken for an "A" or "a" in some default colations.

{Edit}.... I did it again. I didn't read the whole post before I posted. Sorry about the dupe info that Sean already posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)