Solved

SQL Get number of commas in a string

Posted on 2011-03-08
12
1,046 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 300 total points
ID: 35074036

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
ID: 35074045
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
ID: 35074103
@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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 6

Assisted Solution

by:markterry
markterry earned 50 total points
ID: 35074169
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 41

Expert Comment

by:Sharath
ID: 35074351
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
ID: 35074390
'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
 
LVL 1

Author Comment

by:cheryl9063
ID: 35074519
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 41

Assisted Solution

by:Sharath
Sharath earned 50 total points
ID: 35074675
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
ID: 35084685
-- 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
ID: 35084723
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 25 total points
ID: 35084831
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
ID: 35084899
Thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need to find substring in SQL 5 44
Find unused columns in a table 12 75
Many to one in one row 2 40
Display field if column exists 7 35
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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