Count the number of spaces in a field
-
Hi!
I have a table that has a Procedures field which includes several numbers separated by a space (i.e. 99202 10060 99000 A6402). I need to count the number of procedures in this field and I am assuming the best way would be to count the number of spaces and add 1 to it. Only problem is I don't know how to count the spaces or if it can be done. Can anyone offer assistance?
Mishelle
Western Skies Billing Service
-
--function that returns number of spaces in string
create function GetSpaceCount(@SearchString nvarchar(2000))
returns int
as
begin
declare @SpacePos int,@SpaceCount int;
set @SpacePos=0;
set @SpaceCount=0;
set @SpacePos=CHARINDEX(' ',@SearchString,@SpacePos)
while @SpacePos<>0
begin
set @SpacePos=CHARINDEX(' ',@SearchString,@SpacePos+1)
set @SpaceCount=@SpaceCount+1
end
return @SpaceCount;
end
--test
select GetSpaceCount('99202 10060 99000 A6402')
-
Or look at Jeff (RBAR) Moden's article:- http://www.sqlservercentral.com/articles/TSQL/62867/
-
DECLARE @s-2 VARCHAR(30)SET @s-2='99202 10060 99000 A6402'
SELECT LEN(@S)-LEN(REPLACE(@S,' ','')) AS NumberOfSpaces
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you all so much! You totally rock!
-
Mark (9/17/2008)
DECLARE @s-2 VARCHAR(30)SET @s-2='99202 10060 99000 A6402'
SELECT LEN(@S)-LEN(REPLACE(@S,' ','')) AS NumberOfSpaces
THAT's the way to do it... no RBAR, no join! 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's the one I used and it worked marvelously. 🙂
-
Jeff Moden (9/17/2008)
Mark (9/17/2008)
DECLARE @s-2 VARCHAR(30)SET @s-2='99202 10060 99000 A6402'
SELECT LEN(@S)-LEN(REPLACE(@S,' ','')) AS NumberOfSpaces
THAT's the way to do it... no RBAR, no join! 🙂
Except...It will return the wrong number if any of the spaces are at the end.......
Try
SELECT LEN(@S+'.')-LEN(REPLACE(@S+'.',' ',''))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Good point. But what does the + '.' do?
-
It makes sure that the trailing spaces don't get truncated before you count them. (LEN will not count trailing spaces). by adding it to both - it cancels out in the count.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Gotcha! Thanks for the info! That is very helpful.
-
Matt Miller (9/18/2008)
Jeff Moden (9/17/2008)
Mark (9/17/2008)
DECLARE @s-2 VARCHAR(30)SET @s-2='99202 10060 99000 A6402'
SELECT LEN(@S)-LEN(REPLACE(@S,' ','')) AS NumberOfSpaces
THAT's the way to do it... no RBAR, no join! 🙂
Except...It will return the wrong number if any of the spaces are at the end.......
Try
SELECT LEN(@S+'.')-LEN(REPLACE(@S+'.',' ',''))
You could always use DATALENGTH() instead. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
But consider that for unicode strings DATALENGTH = 2*LEN;)
Best Regards,
Chris Büttner
-
Matt Miller (9/18/2008)
It makes sure that the trailing spaces don't get truncated before you count them. (LEN will not count trailing spaces). by adding it to both - it cancels out in the count.)But doesn't the OP want to count the number of items in the list by counting the spaces BETWEEN them and then adding 1? In that case you don't want to count the trailing spaces.
TerriTo speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply