Solved

SQL Get number of commas in a string

Posted on 2011-03-08
12
1,035 Views
Last Modified: 2012-05-11
I have a variable called @location that could have data like: 5222 michaux rd, GSO, NC,27410.. I want to know how many commas are in it..answer is 3 of course..Should I use patindex?
0
Comment
Question by:cheryl9063
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 300 total points
Comment Utility

declare @Count int
declare @string varchar(50)
set @string = 'rd, GSO, NC, 27410'

SELECT @count = LEN(@string) - LEN(REPLACE(@string, ',', ''))
select @Count

//replace @string with your field name
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 50 total points
Comment Utility
if your variable is < 2048 characters then this will tell you how many commas it contains...

select count(*) as num_commas
  from
(select @yourvar as fld) as x
cross join master.dbo.spt_values as v
where v.type='p'
and v.number between 1 and len(x.fld)
and substring(fld,v.number,1)=','
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
@ewangoya  unfortunately that would not work consistently...
SELECT @count = LEN(@string) - LEN(REPLACE(@string, ',', ''))


e.g consider string 'a, , ,'

replacing the commas with space / emopty string would lead LEN() to report it as length 1 not the 3 you were hoping for
you could use DATALENGTH and replace with an empty string

if Varchar
SELECT @count = DATALENGTH(@string) - DATALENGTH(REPLACE(@string, ',', ''))
if NVarchar
SELECT @count = (DATALENGTH(@string) - DATALENGTH(REPLACE(@string, ',', '')))/2


0
 
LVL 6

Assisted Solution

by:markterry
markterry earned 50 total points
Comment Utility
I cannot think of a way to do this without doing it recursively.

Declare @Location varchar(50), @CntComma int
Set @Location = '5222 michaux rd, GSO, NC,27410'
Set @CntComma = 0

While @Location like '%,%'
BEGIN
Set @Location = substring(@Location,1, PATINDEX('%,%', @Location)-1) + substring(@Location,PATINDEX('%,%', @Location)+1, LEN(@Location))
Set @CntComma +=1
END


select @Location, @CntComma
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Lowfatspread - Not sure on why did you tell that won't work.
I tried ewangoya's query for string 'a,,,' and got the count as 3.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
'a    ,       ,        ,'


try it with spaces  between the comma at the end of the string.....

LEN ignores spaces   so if you remove the last/trailing comma you reduce the reported LEN() value by more than 1....

use datalength()  for consistency and cater for bytes/characters depending on the datatype.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
Thanks.. this will ALWAYS be an address so some different scenerios would be like



5222 michaux rd GSO, NC,27410

or

5222 michaux rd GSO NC 27410
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 50 total points
Comment Utility
In that case, I think this is sufficient.
declare @Count int
declare @string varchar(50)
set @string = 'a    ,       ,        ,'
set @string = REPLACE(@string,' ','')
SELECT @count = LEN(@string) - LEN(REPLACE(@string, ',', ''))
select @Count
-- 3

Open in new window

0
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 25 total points
Comment Utility
-- Here is yet another way ... it is a recursive query, but doesn't use a LOOP construct or an external table:

declare @data varchar(MAX), @delim varchar(MAX)
select  @data = 'red,orange,yellow,green,blue,violet', @delim=','

;  -- this semicolon is necessary for this syntax, to separate the WITH as a new statement:

with v (rowid,str,segment,delimPos) as (
  select 1 as rowid, @data as str, substring(@data,1, case when CHARINDEX(@delim,@data)=0 then LEN(@data) else CHARINDEX(@delim,@data)-1 end ) as segment, CHARINDEX(@delim,@data) as delimPos
  union all
  select v.rowid+1, v.str, substring(v.str,v.delimPos+LEN(@delim), case when CHARINDEX(@delim,v.str,v.delimPos+1)-v.delimPos+1 < 0 then LEN(v.str) else CHARINDEX(@delim,v.str,v.delimPos+1)-v.delimPos-LEN(@delim) end ) as segment, CHARINDEX(@delim,v.str,v.delimPos+1) as delimPos
  from v
  where v.delimPos > 0
)

select COUNT(*)-1 as comma_count
--select rowid, segment
from v

0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
The real point of the query above is to parse the delimited string into a table, but using count you can also derive the comma count.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 25 total points
Comment Utility
just to comment on the "trailing spaces" for the LEN + REPLACE method...
nothing easier to avoid that "issue" with:
declare @Count int
declare @string varchar(50)
set @string = 'a    ,       ,        ,'

SELECT @count = LEN(@string +'x') - LEN(REPLACE(@string + 'x', ',', ''))
select @Count
-- 3 

Open in new window


just my 3cents
0
 
LVL 1

Author Closing Comment

by:cheryl9063
Comment Utility
Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now