Solved

SQL Get number of commas in a string

Posted on 2011-03-08
12
1,037 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
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
 
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 40

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 40

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 142

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

911 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

24 Experts available now in Live!

Get 1:1 Help Now