• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1078
  • Last Modified:

SQL Get number of commas in a string

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
cheryl9063
Asked:
cheryl9063
  • 3
  • 2
  • 2
  • +4
6 Solutions
 
Ephraim WangoyaCommented:

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
 
LowfatspreadCommented:
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
 
LowfatspreadCommented:
@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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
markterryCommented:
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
 
SharathData EngineerCommented:
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
 
LowfatspreadCommented:
'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
 
cheryl9063Author Commented:
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
 
SharathData EngineerCommented:
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
 
knightEknightCommented:
-- 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
 
knightEknightCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
cheryl9063Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now