• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1062
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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