Count the number of spaces in a field

Question

Count the number of spaces in a field

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Terri (9/19/2008)
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.

You do want to count them, but you want to cancel them out. Otherwise the replace function will catch them and it will ruin the count.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

steveb (9/19/2008)

Terri (9/19/2008)
You do want to count them, but you want to cancel them out. Otherwise the replace function will catch them and it will ruin the count.

Why not use inbuilt functions?

DECLARE @String VARCHAR(5) -- OR CHAR(5)

SET @String = ' 3 '

SELECT LEN(LTRIM(RTRIM(@String)))

Result = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/21/count-number-of-words-in-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

steveb (9/19/2008)
Terri (9/19/2008)
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.
You do want to count them, but you want to cancel them out. Otherwise the replace function will catch them and it will ruin the count.

Hmm... I hadn't caught why we were counting spaces. In either case - it could be a crapshoot, if there are multiple contiguous spaces. Same for "fake" spaces (non-printing characters, which might show as a space).

You'd actually be better off doing a "word count", but that would require Regex which means CLR. Or - some fancy footwork to remove all of the invalid permutations (like the double spaces).

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 5

mbookout (9/16/2008)
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

I do have to ask... why do you want to simply count the number of numbers? What is it that you need such a count for?

--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)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Nopes...Buddy it wont gives different result if spaces added at the ends

try this

select len('99202 10060 99000 A6402'),len('99202 10060 99000 A6402 ')

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Mishelle SSC Journeyman Points: 92 More actions · Answer 7

I was pulling information from a table that has claims that were sent to the insurance company and in this one field it lists all the procedure codes that were billed in that claim. We have a client who wanted reporting on the average # of procedures billed to the number of claims sent. I could have gotten this information through joining several tables, but it was all nice and neat in this one table as long as I could count the number of procedure codes that were in that field.

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 8

mbookout (9/22/2008)
I was pulling information from a table that has claims that were sent to the insurance company and in this one field it lists all the procedure codes that were billed in that claim. We have a client who wanted reporting on the average # of procedures billed to the number of claims sent. I could have gotten this information through joining several tables, but it was all nice and neat in this one table as long as I could count the number of procedure codes that were in that field.

Ah... good thinking. Thanks for the feedback. 🙂

--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)