Count spaces in string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Count spaces in string

Author

Topic

rnaylor
Starting Member

7 Posts

Posted - 2006-03-15 : 16:05:54

I have a field stored in an old database that places machines in a field seperated by spaces. Is there a way that I can count these machines in the field.

For example, the field stores data like this:

U1 U2 U5 U6 U8

I would like a way to have a field in a view that says total number of machines and have it return 5. This field can hold up to 50 machines or as little as 0.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-15 : 16:15:44

assuming the values are seperated by 1 space how about:


select len(machines) - len(replace(machines, ' ', '')) + 1 machineCount

from	(
	select 'U1 U2 U5 U6 U8' machines
	) a

output:

machineCount 
------------ 
5

Be One with the Optimizer
TG

rnaylor
Starting Member

7 Posts

Posted - 2006-03-15 : 16:37:11

Great! Thanks for the help. I had to modify it a bit to handle the null and blank space saved in this field to the following:

lblMachineCount=
CASE
WHEN LEN(machine) IS NULL THEN '0'
WHEN LEN(machine) = '' THEN '0'
ELSE
LEN(machine) - LEN(REPLACE(machine, ' ', '')) + 1
END,

Do you see any problems with this? It appears to work great.

Thanks again.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-15 : 17:04:39

Avoid boolean logic if you can:

declare	@Machines varchar(50)
set	@Machines = ' U1 U2 U5 U6 U8 '

select	coalesce(len('x' + ltrim(@Machines)), '') - len(coalesce(replace(@Machines, ' ', ''), ''))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 18:01:31

"I had to modify it a bit to handle the null and blank space saved in this field"
You can use isnull()

select 	isnull(len(machines) - len(replace(machines, ' ', '')) + 1, 0) machineCount

from	(
	select 'U1 U2 U5 U6 U8' machines union all
	select null
	) a

rnaylor
Starting Member

7 Posts

Posted - 2006-03-16 : 10:49:40

quote:

Originally posted by blindman

Avoid boolean logic if you can:

declare	@Machines varchar(50)
set	@Machines = ' U1 U2 U5 U6 U8 '

select	coalesce(len('x' + ltrim(@Machines)), '') - len(coalesce(replace(@Machines, ' ', ''), ''))

Not sure how to do this in the code in a view. The @Machines would be a database field and would need to go through the entire table and put the value into the variable.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-16 : 11:19:12

Replace @Machines with your column name. @Machines is just for demonstration purposes.

rnaylor
Starting Member

7 Posts

Posted - 2006-03-16 : 11:26:26

quote:
Originally posted by blindman

Replace @Machines with your column name. @Machines is just for demonstration purposes.

I apologize for not following. Never tried this method before. This is what I put

declare @Machines varchar(50)
set @Machines = demo.machine <- field name

Is this wrong? I can keep @machines as the variable name but I need a way to make @machines equal the the field.

I tried this and does not seem to do the trick. I am sure I did it incorrectly.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-16 : 13:42:43

[code]select coalesce(len('x' + ltrim(Machines)), '') - len(coalesce(replace(Machines, ' ', ''), ''))
from [YourTable][/code]This is pretty basic, rnaylor. Please review the section on SELECT statements in Books Online.

rnaylor
Starting Member

7 Posts

Posted - 2006-03-16 : 14:03:10

quote:
Originally posted by blindman

select coalesce(len('x' + ltrim(Machines)), '') - len(coalesce(replace(Machines, ' ', ''), ''))
from [YourTable]
This is pretty basic, rnaylor. Please review the section on SELECT statements in Books Online.

it is not about the select statement, i believe I know how to do this. it is about the variable you are declaring. I dont think this is necessary, at least not sure why it is used. The view code i wrote is 154 lines long and joins 6 different tables and runs fine without this segment of code. Instead of using this varible @Machine, why not do the following:

COALESCE(LEN('x' + LTRIM(equipment.unit)), '') - LEN(COALESCE(REPLACE(equipment.unit, ' ', ''), '')) AS lblUnitCountTest,

where equipment is the table name and unit is the field? I dont see the need for the @machine variable to be declared and set?

Anyways, thanks for your responses, I believe the original way I posted works well for the amount of records returned.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-16 : 14:42:55

quote:
Originally posted by blindman

Replace @Machines with your column name. @Machines is just for demonstration purposes.

@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.
@Machines is just for demonstration purposes.

Now, do you get it?

rnaylor
Starting Member

7 Posts

Posted - 2006-03-16 : 17:09:58

dude, you need to calm down and READ!

I dont care if you call the variable @machine, @cat, @car!!!!

MY POINT IS...... there is no need for a damn variable when all you have to do is put the field name equipment.unit in the statement you posted. You said in your original post stay away from "boolean logic if you can" and then posted this:

declare @Machines varchar(50)
set @Machines = ' U1 U2 U5 U6 U8 '

select coalesce(len('x' + ltrim(@Machines)), '') - len(coalesce(replace(@Machines, ' ', ''), ''))

why use declare and set and just SKIP that and put in the field name as follows:

LESCE(LEN('x' + LTRIM(equipment.unit)), '') - LEN(COALESCE(REPLACE(equipment.unit, ' ', ''), '')) AS lblUnitCountTest,

it does the same thing. relax man, i understand that @machine is for demonstration purposes. don't belittle me man, if i was not clear try to find out what i am saying.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-16 : 18:10:59

quote:
Originally posted by rnaylor
...dude, you need to calm down and READ!...

Wow. You really need to follow your own advice.

CODO ERGO SUM

rnaylor
Starting Member

7 Posts

Posted - 2006-03-16 : 19:16:33

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by rnaylor
...dude, you need to calm down and READ!...

Wow. You really need to follow your own advice.

CODO ERGO SUM

ok...the solution was found in the third post. it works...just trying to make the other solution work to see if it would work better. i appreciate the help and i believe this question is closed.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-17 : 09:11:45

quote:
Originally posted by rnaylor

MY POINT IS...... there is no need for a damn variable when all you have to do is put the field name equipment.unit in the statement you posted.

@MACHINES IS JUST FOR DEMONSTRATION PURPOSES!

Select dbo.getclue()...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources